<?php
class ModelCommunityMcp extends Model{
    /**************main start**************/
    public function getLatestApprovalPostTotal($has_global_privilege){
        $sql="SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_posts WHERE post_approved='0'";

        if(!$has_global_privilege){
            $sql.=" AND forum_id IN (" . $this->getModedForumIds($this->customer->getForumUserId()) . ")";
        }

        $query=$this->db->query($sql);

        return $query->row['total'];
    }


    public function getLatestApprovalPosts($has_global_privilege){
        $sql="SELECT p.post_id, p.post_subject, p.post_time, t.topic_id, t.topic_first_post_id, t.topic_title, f.forum_id, fn.forum_name, u.user_id, u.username FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=p.poster_id) WHERE p.post_approved='0'";

        if(!$has_global_privilege){
            $sql.=" AND p.forum_id IN (" . $this->getModedForumIds($this->customer->getForumUserId()) . ")";
        }

        $sql.=" ORDER BY p.post_time DESC LIMIT 0,5";

        $query=$this->db->query($sql);

        return $query->rows;
    }

    public function getReportTotal($has_global_privilege){
        $sql="SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_reports WHERE report_closed='0' AND pm_id='0'";

        if(!$has_global_privilege){
            $sql.=" AND forum_id IN (" . $this->getModedForumIds($this->customer->getForumUserId()) . ")";
        }

        $query=$this->db->query($sql);

        return $query->row['total'];
    }

    public function getLatestReports($has_global_privilege){
        $sql="SELECT r.report_time, ru.user_id AS reporter_id, ru.username AS reporter_name, p.post_id, p.post_subject, p.post_time, f.forum_id, fn.forum_name, u.user_id, u.username FROM " . DB_PREFIX . "community_reports AS r LEFT JOIN " . DB_PREFIX . "community_posts AS p ON (p.post_id=r.post_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=p.poster_id) LEFT JOIN " . DB_PREFIX . "community_users AS ru ON (ru.user_id=r.user_id) WHERE r.report_closed='0' AND r.pm_id='0'";

        if(!$has_global_privilege){
            $sql.=" AND r.forum_id IN (" . $this->getModedForumIds($this->customer->getForumUserId()) . ")";
        }

        $sql.=" ORDER BY r.report_time DESC LIMIT 0,5";

        $query=$this->db->query($sql);

        return $query->rows;
    }

    public function getLatestPmReports(){
        $query=$this->db->query("SELECT r.report_id, r.report_time, ru.user_id AS reporter_id, ru.username AS reporter_name, p.msg_id, p.message_subject, p.message_time, p.to_address, u.user_id, u.username FROM " . DB_PREFIX . "community_reports AS r LEFT JOIN " . DB_PREFIX . "community_privmsgs AS p ON (p.msg_id=r.pm_id) LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=p.author_id) LEFT JOIN " . DB_PREFIX . "community_users AS ru ON (ru.user_id=r.user_id) WHERE r.report_closed='0' AND r.post_id='0' ORDER BY r.report_time DESC LIMIT 0,5");
        return $query->rows;
    }

    public function getForumInfo($getData){
        $query=$this->db->query("SELECT f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_forum AS f LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE f.forum_id='" . (int)$getData['fid'] . "' LIMIT 0,1");

        return $query->row;
    }

    private function getModedForumIds($user_id){
        $query=$this->db->query("SELECT forum_id, forum_mod_ids FROM " . DB_PREFIX . "community_forum WHERE forum_type='1' AND forum_enabled='1'");

        $forum_id_str='';

        foreach($query->rows as $result){
            if($result['forum_mod_ids'] && in_array($user_id,unserialize($result['forum_mod_ids']))){
                $forum_id_str.=$result['forum_id'] . ',';
            }
        }

        $forum_id_str=rtrim($forum_id_str,',');

        return $forum_id_str;
    }

    public function getForums($has_global_privilege){
        $sql="SELECT f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_forum AS f LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE f.forum_type='1' AND f.forum_enabled='1' AND f.parent_id='0'";

        if(!$has_global_privilege){
            $forum_id_str=$this->getModedParentForumIds($this->customer->getForumUserId());

            if($forum_id_str){
                $sql.=" AND f.forum_id IN (" . $forum_id_str . ") ORDER BY fn.forum_name ASC";

                $query=$this->db->query($sql);

                return $query->rows;
            }else{
                return array();
            }
        }else{
            $sql.=" ORDER BY fn.forum_name ASC";

            $query=$this->db->query($sql);

            return $query->rows;
        }
    }

    public function getSubForums($has_global_privilege,$parentId){
        $sql="SELECT f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_forum AS f LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE f.forum_type='1' AND f.forum_enabled='1' AND f.parent_id='" . (int)$parentId . "'";

        if(!$has_global_privilege){
            $forum_id_str=$this->getModedSubForumIds($this->customer->getForumUserId(),$parentId);

            if($forum_id_str){
                $sql.=" AND f.forum_id IN (" . $forum_id_str . ") ORDER BY fn.forum_name ASC";

                $query=$this->db->query($sql);

                return $query->rows;
            }else{
                return array();
            }
        }else{
            $sql.=" ORDER BY fn.forum_name ASC";

            $query=$this->db->query($sql);

            return $query->rows;
        }
    }

    public function getDestinationForums(){
        $query=$this->db->query("SELECT f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_forum AS f LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE f.forum_type='1' AND f.forum_enabled='1' AND f.parent_id='0' ORDER BY fn.forum_name ASC");

        return $query->rows;
    }

    public function getDestinationSubForums($parentId){
        $query=$this->db->query("SELECT f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_forum AS f LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE f.forum_type='1' AND f.forum_enabled='1' AND f.parent_id='" . (int)$parentId . "' ORDER BY fn.forum_name ASC");

        return $query->rows;

    }

    private function getModedParentForumIds($user_id){
        $query=$this->db->query("SELECT forum_id, forum_mod_ids FROM " . DB_PREFIX . "community_forum WHERE forum_type='1' AND forum_enabled='1' AND parent_id='0'");

        $forum_id_str='';

        foreach($query->rows as $result){
            if($result['forum_mod_ids'] && in_array($user_id,unserialize($result['forum_mod_ids']))){
                $forum_id_str.=$result['forum_id'] . ',';
            }
        }

        $forum_id_str=rtrim($forum_id_str,',');

        return $forum_id_str;
    }

    private function getModedSubForumIds($user_id,$parentId){
        $query=$this->db->query("SELECT forum_id, forum_mod_ids FROM " . DB_PREFIX . "community_forum WHERE forum_type='1' AND forum_enabled='1' AND parent_id='" . (int)$parentId . "'");

        $sub_forum_id_str='';

        foreach($query->rows as $result){
            if($result['forum_mod_ids'] && in_array($user_id,unserialize($result['forum_mod_ids']))){
                $sub_forum_id_str.=$result['forum_id'] . ',';
            }
        }

        $sub_forum_id_str=rtrim($sub_forum_id_str,',');

        return $sub_forum_id_str;
    }

    public function getTopicTotal($getData){
        $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_topics WHERE forum_id='" . (int)$getData['fid'] . "' AND topic_deleted='0'");

        return $query->row['total'];
    }

    public function getTopics($getData,$start,$limit){
        $query=$this->db->query("SELECT t.topic_id, t.forum_id, t.topic_approved, t.topic_reported, t.topic_title, t.topic_poster_id, t.topic_time, t.topic_status, t.topic_type, t.topic_first_poster_name, t.topic_last_poster_id, t.topic_last_poster_name, t.topic_last_post_time FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_topics_track AS tt ON (t.topic_id=tt.topic_id AND tt.user_id='" . (int)$this->customer->getForumUserId() . "') WHERE t.forum_id='" . (int)$getData['fid'] . "' AND t.topic_deleted='0' ORDER BY t.topic_type DESC, t.topic_last_post_time DESC LIMIT " . $start . ", " . $limit);

        return $query->rows;
    }

    public function forumModerateAction($postData,$getData){
        switch($postData['moderate-action']){
            case 'd'://delete topic
                foreach($postData['marked_t_id'] as $tid){
                    $query=$this->db->query("SELECT post_id, poster_id FROM " . DB_PREFIX . "community_posts WHERE topic_id='" . (int)$tid . "' AND post_deleted='0' ORDER BY post_time DESC");

                    $i=0;
                    $post_id_str='';

                    foreach($query->rows as $result){
                        $this->db->query("UPDATE " . DB_PREFIX . "community_users SET user_posts=user_posts-1 WHERE user_id='" . (int)$result['poster_id'] . "'");

                        $post_id_str.=$result['post_id'] . ',';

                        $i++;
                    }

                    $post_id_str=rtrim($post_id_str,',');

                    $this->db->query("UPDATE " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_posts AS p ON (p.post_id IN (" . $post_id_str . ")) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=t.forum_id) LEFT JOIN " . DB_PREFIX . "community_users AS tu ON (tu.user_id=t.topic_poster_id) LEFT JOIN " . DB_PREFIX . "community_poll_options AS po ON (po.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_poll_votes AS pv ON (pv.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_topic_notify AS tn ON (tn.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_bookmarks AS b ON (b.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "setting AS stt ON (stt.`key`='total_topics') LEFT JOIN " . DB_PREFIX . "setting AS stp ON (stp.`key`='total_posts') SET p.post_deleted='1', t.topic_deleted='1', f.forum_topics=f.forum_topics-1, f.forum_posts=f.forum_posts-" . (int)$i . ", tu.user_topics=tu.user_topics-1, po.topic_deleted='1', pv.topic_deleted='1', tn.topic_deleted='1', b.topic_deleted='1', stt.`value`=stt.`value`-1, stp.`value`=stp.`value`-" . (int)$i . " WHERE t.topic_id='" . (int)$tid . "'");

                    //add moderator log
                    $topicQuery=$this->db->query("SELECT f.forum_id, fn.forum_name, t.topic_title, t.topic_poster_id, t.topic_first_poster_name FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (t.forum_id=f.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE t.topic_id='" . (int)$tid . "' LIMIT 0,1");

                    $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='topic_delete', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$topicQuery->row['topic_poster_id'] . "', forum_id='" . (int)$topicQuery->row['forum_id'] . "', topic_id='" . (int)$tid . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($topicQuery->row['topic_title']),$topicQuery->row['topic_poster_id'],$this->db->escape($topicQuery->row['topic_first_poster_name']),$this->db->escape($topicQuery->row['forum_name']))) . "'");

                    //clear sql cache
                    $encrypt_data=md5($tid);
                    $this->sql_cache->delete('viewtopic.posttotal.' . $encrypt_data);
                    $this->sql_cache->delete('viewtopic.postids.' . $encrypt_data);
                    $this->sql_cache->delete('viewtopic.topicinfo.' . $encrypt_data);
                    $this->sql_cache->delete('viewtopic.posts.' . $encrypt_data);
                }

                //clear sql cache
                $this->sql_cache->delete('viewforum.topics.' . md5($getData['fid']));
                $this->sql_cache->delete('viewforum.topictotal.' . md5($getData['fid']));
                $this->sql_cache->delete('viewforum.announcementtopics.');
                $this->sql_cache->delete('community.foruminfo.');
                break;
            case 'm':
                foreach($postData['marked_t_id'] as $tid){
                    //add moderator log
                    $topicQuery=$this->db->query("SELECT f.forum_id, fn.forum_name, df.forum_id AS move_forum_id, dfn.forum_name AS move_forum_name, t.topic_title, t.topic_poster_id, t.topic_first_poster_name FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (t.forum_id=f.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS dfn ON (dfn.forum_id='" . (int)$postData['dfid'] . "' AND dfn.language_id='" . (int)$this->config->get('config_language_id') . "')  WHERE t.topic_id='" . (int)$tid . "' LIMIT 0,1");

                    $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='topic_move', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$topicQuery->row['topic_poster_id'] . "', forum_id='" . (int)$topicQuery->row['forum_id'] . "', topic_id='" . (int)$tid . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($topicQuery->row['topic_title']),$topicQuery->row['topic_poster_id'],$this->db->escape($topicQuery->row['topic_first_poster_name']),$this->db->escape($topicQuery->row['forum_name']),$this->db->escape($topicQuery->row['move_forum_id']),$this->db->escape($topicQuery->row['move_forum_name']))) . "'");

                    // move topic
                    $query=$this->db->query("SELECT post_id, poster_id FROM " . DB_PREFIX . "community_posts WHERE topic_id='" . (int)$tid . "' AND post_deleted='0' ORDER BY post_time DESC");

                    $i=0;
                    $post_id_str='';

                    foreach($query->rows as $result){
                        $post_id_str.=$result['post_id'] . ',';

                        $i++;
                    }

                    $post_id_str=rtrim($post_id_str,',');

                    $this->db->query("UPDATE " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_posts AS p ON (p.post_id IN (" . $post_id_str . ")) LEFT JOIN " . DB_PREFIX . "community_forum AS of ON (of.forum_id=t.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum AS df ON (df.forum_id='" . (int)$postData['dfid'] . "') LEFT JOIN " . DB_PREFIX . "community_topics_track AS tt ON (tt.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_drafts AS d ON (d.topic_id=t.topic_id) SET p.forum_id='" . (int)$postData['dfid'] . "', t.forum_id='" . (int)$postData['dfid'] . "', of.forum_topics=of.forum_topics-1, of.forum_posts=of.forum_posts-" . (int)$i . ", df.forum_topics=df.forum_topics+1, df.forum_posts=df.forum_posts+" . (int)$i . ", tt.forum_id='" . (int)$postData['dfid'] . "', d.forum_id='" . (int)$postData['dfid'] . "' WHERE t.topic_id='" . (int)$tid . "'");
                }

                //clear sql cache
                $this->sql_cache->delete('viewforum.topics.' . md5($getData['fid']));
                $this->sql_cache->delete('viewforum.topictotal.' . md5($getData['fid']));
                $this->sql_cache->delete('viewforum.announcementtopics.');
                $this->sql_cache->delete('community.foruminfo.');
                break;
            case 'l':
                foreach($postData['marked_t_id'] as $tid){
                    $this->db->query("UPDATE " . DB_PREFIX . "community_topics SET topic_status='1'  WHERE topic_id='" . (int)$tid . "'");

                    //add moderator log
                    $topicQuery=$this->db->query("SELECT f.forum_id, fn.forum_name, t.topic_title, t.topic_poster_id, t.topic_first_poster_name FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (t.forum_id=f.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE t.topic_id='" . (int)$tid . "' LIMIT 0,1");

                    $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='topic_lock', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$topicQuery->row['topic_poster_id'] . "', forum_id='" . (int)$topicQuery->row['forum_id'] . "', topic_id='" . (int)$tid . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($topicQuery->row['topic_title']),$topicQuery->row['topic_poster_id'],$this->db->escape($topicQuery->row['topic_first_poster_name']),$this->db->escape($topicQuery->row['forum_name']))) . "'");

                    //clear sql cache
                    $this->sql_cache->delete('viewtopic.topicinfo.' . md5($tid));
                }

                //clear sql cache
                $this->sql_cache->delete('viewforum.topics.' . md5($getData['fid']));
                break;
            case 'u':
                foreach($postData['marked_t_id'] as $tid){
                    $this->db->query("UPDATE " . DB_PREFIX . "community_topics SET topic_status='0'  WHERE topic_id='" . (int)$tid . "'");

                    //add moderator log
                    $topicQuery=$this->db->query("SELECT f.forum_id, fn.forum_name, t.topic_title, t.topic_poster_id, t.topic_first_poster_name FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (t.forum_id=f.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE t.topic_id='" . (int)$tid . "' LIMIT 0,1");

                    $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='topic_unlock', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$topicQuery->row['topic_poster_id'] . "', forum_id='" . (int)$topicQuery->row['forum_id'] . "', topic_id='" . (int)$tid . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($topicQuery->row['topic_title']),$topicQuery->row['topic_poster_id'],$this->db->escape($topicQuery->row['topic_first_poster_name']),$this->db->escape($topicQuery->row['forum_name']))) . "'");

                    //clear sql cache
                    $this->sql_cache->delete('viewtopic.topicinfo.' . md5($tid));
                }

                //clear sql cache
                $this->sql_cache->delete('viewforum.topics.' . md5($getData['fid']));
                break;
            case 'f':
                foreach($postData['marked_t_id'] as $tid){
                    $this->db->query("UPDATE " . DB_PREFIX . "community_topics SET topic_featured='1'  WHERE topic_id='" . (int)$tid . "'");

                    //add forum post for featured post
                    $customer_query=$this->db->query("SELECT u.user_id, c.master_slave FROM " . DB_PREFIX . "community_users AS u LEFT JOIN " . DB_PREFIX . "customer AS c ON (c.customer_id=u.customer_id) WHERE u.user_id=(SELECT topic_poster_id FROM " . DB_PREFIX . "community_topics WHERE topic_id='" . (int)$tid . "' LIMIT 0,1) LIMIT 0,1");

                    if($customer_query->row['master_slave']!='sl'){
                        $this->db->query("INSERT INTO " . DB_PREFIX . "community_forum_point_history SET user_id='" . (int)$customer_query->row['user_id'] . "', type='4', description='" . $this->db->escape('Featured Post Awarded Forum Points') . "', points='" . (int)$this->config->get('featured_topic_awarded_forum_points') . "', add_time='" . time() . "'");

                        $this->db->query("UPDATE " . DB_PREFIX . "community_users SET forum_points=forum_points+" . (int)$this->config->get('featured_topic_awarded_forum_points') . " WHERE user_id='" . (int)$customer_query->row['user_id'] . "'");
                    }

                    //add moderator log
                    $topicQuery=$this->db->query("SELECT f.forum_id, fn.forum_name, t.topic_title, t.topic_poster_id, t.topic_first_poster_name FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (t.forum_id=f.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  WHERE t.topic_id='" . (int)$tid . "' LIMIT 0,1");

                    $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='topic_feature', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$topicQuery->row['topic_poster_id'] . "', forum_id='" . (int)$topicQuery->row['forum_id'] . "', topic_id='" . (int)$tid . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($topicQuery->row['topic_title']),$topicQuery->row['topic_poster_id'],$this->db->escape($topicQuery->row['topic_first_poster_name']),$this->db->escape($topicQuery->row['forum_name']))) . "'");
                }

                //clear sql cache
                $this->sql_cache->delete('viewforum.topics.' . md5($getData['fid']));
                $this->sql_cache->delete('viewforum.announcementtopics.');
                break;
            case 'sd':
                foreach($postData['marked_t_id'] as $tid){
                    $this->db->query("UPDATE " . DB_PREFIX . "community_topics SET topic_type='0'  WHERE topic_id='" . (int)$tid . "'");

                    //add moderator log
                    $topicQuery=$this->db->query("SELECT f.forum_id, fn.forum_name, t.topic_title, t.topic_poster_id, t.topic_first_poster_name FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (t.forum_id=f.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  WHERE t.topic_id='" . (int)$tid . "' LIMIT 0,1");

                    $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='topic_standard', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$topicQuery->row['topic_poster_id'] . "', forum_id='" . (int)$topicQuery->row['forum_id'] . "', topic_id='" . (int)$tid . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($topicQuery->row['topic_title']),$topicQuery->row['topic_poster_id'],$this->db->escape($topicQuery->row['topic_first_poster_name']),$this->db->escape($topicQuery->row['forum_name']))) . "'");
                }

                //clear sql cache
                $this->sql_cache->delete('viewforum.topics.' . md5($getData['fid']));
                $this->sql_cache->delete('viewforum.announcementtopics.');
                break;
            case 'st':
                foreach($postData['marked_t_id'] as $tid){
                    $this->db->query("UPDATE " . DB_PREFIX . "community_topics SET topic_type='1', topic_time_limit='" . (int)(floor($postData['sticky-time'])*60*60*24+time()) . "' WHERE topic_id='" . (int)$tid . "'");

                    //add moderator log
                    $topicQuery=$this->db->query("SELECT f.forum_id, fn.forum_name, t.topic_title, t.topic_poster_id, t.topic_first_poster_name FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (t.forum_id=f.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  WHERE t.topic_id='" . (int)$tid . "' LIMIT 0,1");

                    $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='topic_stick', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$topicQuery->row['topic_poster_id'] . "', forum_id='" . (int)$topicQuery->row['forum_id'] . "', topic_id='" . (int)$tid . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($topicQuery->row['topic_title']),$topicQuery->row['topic_poster_id'],floor($postData['sticky-time']),$this->db->escape($topicQuery->row['topic_first_poster_name']),$this->db->escape($topicQuery->row['forum_name']))) . "'");
                }

                //clear sql cache
                $this->sql_cache->delete('viewforum.topics.' . md5($getData['fid']));
                $this->sql_cache->delete('viewforum.announcementtopics.');
                break;
            case 'a':
                foreach($postData['marked_t_id'] as $tid){
                    $this->db->query("UPDATE " . DB_PREFIX . "community_topics SET topic_type='2', topic_time_limit='" . (int)(floor($postData['sticky-time'])*60*60*24+time()) . "' WHERE topic_id='" . (int)$tid . "'");

                    //add moderator log
                    $topicQuery=$this->db->query("SELECT f.forum_id, fn.forum_name, t.topic_title, t.topic_poster_id, t.topic_first_poster_name FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (t.forum_id=f.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  WHERE t.topic_id='" . (int)$tid . "' LIMIT 0,1");

                    $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='topic_announce', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$topicQuery->row['topic_poster_id'] . "', forum_id='" . (int)$topicQuery->row['forum_id'] . "', topic_id='" . (int)$tid . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($topicQuery->row['topic_title']),$topicQuery->row['topic_poster_id'],floor($postData['sticky-time']),$this->db->escape($topicQuery->row['topic_first_poster_name']),$this->db->escape($topicQuery->row['forum_name']))) . "'");
                }

                //clear sql cache
                $this->sql_cache->delete('viewforum.topics.' . md5($getData['fid']));
                $this->sql_cache->delete('viewforum.announcementtopics.');
                break;
            case 'g':
                foreach($postData['marked_t_id'] as $tid){
                    $this->db->query("UPDATE " . DB_PREFIX . "community_topics SET topic_type='3', topic_time_limit='" . (int)(floor($postData['sticky-time'])*60*60*24+time()) . "' WHERE topic_id='" . (int)$tid . "'");

                    //add moderator log
                    $topicQuery=$this->db->query("SELECT f.forum_id, t.topic_title, t.topic_poster_id, t.topic_first_poster_name FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (t.forum_id=f.forum_id) WHERE t.topic_id='" . (int)$tid . "' LIMIT 0,1");

                    $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='topic_globally_announce', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$topicQuery->row['topic_poster_id'] . "', forum_id='" . (int)$topicQuery->row['forum_id'] . "', topic_id='" . (int)$tid . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($topicQuery->row['topic_title']),$topicQuery->row['topic_poster_id'],floor($postData['sticky-time']),$this->db->escape($topicQuery->row['topic_first_poster_name']))) . "'");
                }

                //clear sql cache
                $this->sql_cache->delete('viewforum.topics.' . md5($getData['fid']));
                $this->sql_cache->delete('viewforum.announcementtopics.');
                break;
        }
    }

    public function getTopicInfo($getData){
        $query=$this->db->query("SELECT topic_first_post_id, topic_last_post_id FROM " . DB_PREFIX . "community_topics WHERE topic_id='" . (int)$getData['tid'] . "' LIMIT 0,1");

        return $query->row;
    }

    public function getPostTotal($getData){
        $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_posts WHERE topic_id='" . (int)$getData['tid'] . "' AND post_deleted='0'");

        return $query->row['total'];
    }

    public function getPosts($getData,$start,$limit){
        $query=$this->db->query("SELECT p.post_reported, p.post_id, p.forum_id, p.topic_id, p.post_subject, p.poster_id, p.post_time, p.post_text, p.enable_bbcode, p.enable_smilies, p.enable_magic_url, u.username, pe.posting_bbcode, pe.posting_bbcode_url, pe.posting_bbcode_img, pe.posting_bbcode_flash, pe.posting_smiles FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=p.poster_id) LEFT JOIN " . DB_PREFIX . "community_permission AS pe ON (pe.forum_id=p.forum_id AND pe.role_id=u.user_type AND pe.rank_id=IF(u.user_type=7,u.user_rank,'0')) WHERE p.topic_id='" . (int)$getData['tid'] . "'AND p.post_deleted='0' ORDER BY p.post_time ASC LIMIT " . $start . "," . $limit);

        return $query->rows;
    }

    public function topicModerateAction($postData,$getData){
        switch($postData['moderate-action']){
            case 'd':
                if(in_array($postData['first_post_id'],$postData['marked_p_id'])){//if delete the first post, delete the whole topic
                    $query=$this->db->query("SELECT post_id, poster_id FROM " . DB_PREFIX . "community_posts WHERE topic_id='" . (int)$getData['tid'] . "' AND post_deleted='0' ORDER BY post_time DESC");

                    $i=0;
                    $post_id_str='';

                    foreach($query->rows as $result){
                        $this->db->query("UPDATE " . DB_PREFIX . "community_users SET user_posts=user_posts-1 WHERE user_id='" . (int)$result['poster_id'] . "'");

                        $post_id_str.=$result['post_id'] . ',';

                        $i++;
                    }

                    $post_id_str=rtrim($post_id_str,',');

                    $this->db->query("UPDATE " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_posts AS p ON (p.post_id IN (" . $post_id_str . ")) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=t.forum_id) LEFT JOIN " . DB_PREFIX . "community_users AS tu ON (tu.user_id=t.topic_poster_id) LEFT JOIN " . DB_PREFIX . "community_poll_options AS po ON (po.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_poll_votes AS pv ON (pv.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_topic_notify AS tn ON (tn.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_bookmarks AS b ON (b.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "setting AS stt ON (stt.`key`='total_topics') LEFT JOIN " . DB_PREFIX . "setting AS stp ON (stp.`key`='total_posts') SET p.post_deleted='1', t.topic_deleted='1', f.forum_topics=f.forum_topics-1, f.forum_posts=f.forum_posts-" . (int)$i . ", tu.user_topics=tu.user_topics-1, po.topic_deleted='1', pv.topic_deleted='1', tn.topic_deleted='1', b.topic_deleted='1', stt.`value`=stt.`value`-1, stp.`value`=stp.`value`-" . (int)$i . " WHERE t.topic_id='" . (int)$getData['tid'] . "'");

                    //add moderator log
                    $topicQuery=$this->db->query("SELECT f.forum_id, fn.forum_name, t.topic_title, t.topic_poster_id, t.topic_first_poster_name FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (t.forum_id=f.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  WHERE t.topic_id='" . (int)$getData['tid'] . "' LIMIT 0,1");

                    $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='topic_delete', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$topicQuery->row['topic_poster_id'] . "', forum_id='" . (int)$topicQuery->row['forum_id'] . "', topic_id='" . (int)$getData['tid'] . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($topicQuery->row['topic_title']),$topicQuery->row['topic_poster_id'],$this->db->escape($topicQuery->row['topic_first_poster_name']),$this->db->escape($topicQuery->row['forum_name']))) . "'");

                    //clear sql cache
                    $this->sql_cache->delete('viewtopic.posttotal.' . md5($getData['tid']));
                    $this->sql_cache->delete('viewtopic.postids.' . md5($getData['tid']));
                    $this->sql_cache->delete('viewtopic.topicinfo.' . md5($getData['tid']));
                    $this->sql_cache->delete('viewtopic.posts.' . md5($getData['tid']));
                    $this->sql_cache->delete('viewforum.topics.' . md5($getData['fid']));
                    $this->sql_cache->delete('viewforum.topictotal.' . md5($getData['fid']));
                    $this->sql_cache->delete('viewforum.announcementtopics.');
                    $this->sql_cache->delete('community.foruminfo.');

                    return TRUE;
                }elseif(in_array($postData['last_post_id'],$postData['marked_p_id'])){//if last post in a topic is deleted
                    foreach($postData['marked_p_id'] as $pid){
                        $this->db->query("UPDATE " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=p.poster_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "setting AS s ON (s.`key`='total_posts') SET p.post_deleted='1', u.user_posts=u.user_posts-1, f.forum_posts=f.forum_posts-1, t.topic_replies=t.topic_replies-1, s.`value`=s.`value`-1 WHERE p.post_id='" . (int)$pid . "'");

                        //add moderator log
                        $postQuery=$this->db->query("SELECT p.post_subject, p.poster_id, p.post_username, t.topic_id, t.topic_title, f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  WHERE p.post_id='" . (int)$pid . "' LIMIT 0,1");

                        $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='post_delete', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$postQuery->row['poster_id'] . "', forum_id='" . (int)$postQuery->row['forum_id'] . "', topic_id='" . (int)$postQuery->row['topic_id'] . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($postQuery->row['post_subject']),$postQuery->row['poster_id'],$this->db->escape($postQuery->row['post_username']),$this->db->escape($postQuery->row['topic_title']),$this->db->escape($postQuery->row['forum_name']))) . "'");
                    }

                    $query=$this->db->query("SELECT post_id, poster_id, post_time, post_username, post_subject FROM " . DB_PREFIX . "community_posts WHERE topic_id=" . (int)$getData['tid'] . " AND post_approved='1' AND post_deleted='0' ORDER BY post_time DESC LIMIT 0,1 ");

                    $postLastOneData=$query->row;

                    $this->db->query("UPDATE " . DB_PREFIX . "community_topics SET topic_last_post_id='" . (int)$postLastOneData['post_id'] . "', topic_last_poster_id='" . (int)$postLastOneData['poster_id'] . "', topic_last_poster_name='" . $this->db->escape($postLastOneData['post_username']) . "', topic_last_post_subject='" . $this->db->escape($postLastOneData['post_subject']) . "', topic_last_post_time='" . $this->db->escape($postLastOneData['post_time']) . "' WHERE topic_id='" . (int)$getData['tid'] . "'");

                    //clear viewtopic sql cache
                    $this->sql_cache->delete('viewtopic.posttotal.' . md5($getData['tid']));
                    $this->sql_cache->delete('viewtopic.postids.' . md5($getData['tid']));
                    $this->sql_cache->delete('viewtopic.posts.' . md5($getData['tid']));
                    $this->sql_cache->delete('viewtopic.topicinfo.' . md5($getData['tid']));
                    $this->sql_cache->delete('viewforum.topics.' . md5($getData['fid']));
                    $this->sql_cache->delete('community.foruminfo.');

                    return FALSE;
                }else{
                    foreach($postData['marked_p_id'] as $pid){
                        $this->db->query("UPDATE " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=p.poster_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "setting AS s ON (s.`key`='total_posts') SET p.post_deleted='1', u.user_posts=u.user_posts-1, f.forum_posts=f.forum_posts-1, t.topic_replies=t.topic_replies-1, s.`value`=s.`value`-1 WHERE p.post_id='" . (int)$pid . "'");

                        //add moderator log
                        $postQuery=$this->db->query("SELECT p.post_subject, p.poster_id, p.post_username, t.topic_id, t.topic_title, f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  WHERE p.post_id='" . (int)$pid . "' LIMIT 0,1");

                        $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='post_delete', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$postQuery->row['poster_id'] . "', forum_id='" . (int)$postQuery->row['forum_id'] . "', topic_id='" . (int)$postQuery->row['topic_id'] . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($postQuery->row['post_subject']),$postQuery->row['poster_id'],$this->db->escape($postQuery->row['post_username']),$this->db->escape($postQuery->row['topic_title']),$this->db->escape($postQuery->row['forum_name']))) . "'");
                    }

                    //clear sql cache
                    $this->sql_cache->delete('viewtopic.posttotal.' . md5($getData['tid']));
                    $this->sql_cache->delete('viewtopic.postids.' . md5($getData['tid']));
                    $this->sql_cache->delete('viewtopic.posts.' . md5($getData['tid']));
                    $this->sql_cache->delete('viewtopic.topicinfo.' . md5($getData['tid']));
                    $this->sql_cache->delete('viewforum.topics.' . md5($getData['fid']));
                    $this->sql_cache->delete('community.foruminfo.');

                    return FALSE;
                }
                break;
            case 'l':
                foreach($postData['marked_p_id'] as $pid){
                    $this->db->query("UPDATE " . DB_PREFIX . "community_posts SET post_edit_locked='1' WHERE post_id='" . (int)$pid . "'");

                    //add moderator log
                    $postQuery=$this->db->query("SELECT p.post_id, p.post_subject, p.poster_id, p.post_username, t.topic_id, t.topic_title, f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  WHERE p.post_id='" . (int)$pid . "' LIMIT 0,1");

                    $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='post_lock', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$postQuery->row['poster_id'] . "', forum_id='" . (int)$postQuery->row['forum_id'] . "', topic_id='" . (int)$postQuery->row['topic_id'] . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($postQuery->row['post_id'],$this->db->escape($postQuery->row['post_subject']),$postQuery->row['poster_id'],$this->db->escape($postQuery->row['post_username']),$this->db->escape($postQuery->row['topic_title']),$this->db->escape($postQuery->row['forum_name']))) . "'");
                }

                //clear sql cache
                $this->sql_cache->delete('viewtopic.posts.' . md5($getData['tid']));
                break;
            case 'u':
                foreach($postData['marked_p_id'] as $pid){
                    $this->db->query("UPDATE " . DB_PREFIX . "community_posts SET post_edit_locked='0' WHERE post_id='" . (int)$pid . "'");

                    //add moderator log
                    $postQuery=$this->db->query("SELECT p.post_id, p.post_subject, p.poster_id, p.post_username, t.topic_id, t.topic_title, f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  WHERE p.post_id='" . (int)$pid . "' LIMIT 0,1");

                    $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='post_unlock', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$postQuery->row['poster_id'] . "', forum_id='" . (int)$postQuery->row['forum_id'] . "', topic_id='" . (int)$postQuery->row['topic_id'] . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($postQuery->row['post_id'],$this->db->escape($postQuery->row['post_subject']),$postQuery->row['poster_id'],$this->db->escape($postQuery->row['post_username']),$this->db->escape($postQuery->row['topic_title']),$this->db->escape($postQuery->row['forum_name']))) . "'");
                }

                //clear sql cache
                $this->sql_cache->delete('viewtopic.posts.' . md5($getData['tid']));
                break;
        }
    }
    /**************main end**************/

    /**************moderation queue start**************/
    public function getApprovalTopicTotal($getData){
        $sql="SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_topics WHERE topic_approved='0'";

        if(isset($getData['fid'])){
            $sql.=" AND forum_id='" . $getData['fid'] . "'";
        }

        $query=$this->db->query($sql);

        return $query->row['total'];
    }

    public function getApprovalTopics($getData,$start,$limit){
        $sql="SELECT t.topic_first_post_id, t.topic_id, t.topic_title, t.topic_time, f.forum_id, fn.forum_name, u.user_id, u.username FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=t.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=t.topic_poster_id) WHERE t.topic_approved='0'";

        if(isset($getData['fid'])){
            $sql.=" AND t.forum_id='" . $getData['fid'] . "'";
        }

        $sql.=" ORDER BY t.topic_time DESC LIMIT " . $start . "," . $limit;

        $query=$this->db->query($sql);

        return $query->rows;
    }

    public function getApprovalPostTotal($getData){
        $sql="SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) WHERE p.post_approved='0' AND (p.post_id>t.topic_first_post_id OR p.post_id<t.topic_first_post_id)";

        if(isset($getData['fid'])){
            $sql.=" AND p.forum_id='" . $getData['fid'] . "'";
        }

        $query=$this->db->query($sql);

        return $query->row['total'];
    }

    public function getApprovalPosts($getData,$start,$limit){
        $sql="SELECT p.post_id, p.post_subject, p.post_time, t.topic_id, t.topic_title, f.forum_id, fn.forum_name, u.user_id, u.username FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=p.poster_id) WHERE p.post_approved='0' AND (p.post_id>t.topic_first_post_id OR p.post_id<t.topic_first_post_id)";

        if(isset($getData['fid'])){
            $sql.=" AND p.forum_id='" . $getData['fid'] . "'";
        }

        $sql.=" ORDER BY p.post_time DESC LIMIT " . $start . "," . $limit;

        $query=$this->db->query($sql);

        return $query->rows;
    }

    public function getApprovalDetailPost($getData){
        $query=$this->db->query("SELECT p.post_id, p.post_subject, p.post_time, post_text, p.enable_bbcode, p.enable_smilies, p.enable_magic_url, t.topic_id, t.topic_first_post_id, f.forum_id, u.user_id, u.username, pe.posting_bbcode, pe.posting_bbcode_url, pe.posting_bbcode_img, pe.posting_bbcode_flash, pe.posting_smiles FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=p.poster_id) LEFT JOIN " . DB_PREFIX . "community_permission AS pe ON (pe.forum_id=p.forum_id AND pe.role_id=u.user_type AND pe.rank_id=IF(u.user_type=7,u.user_rank,'0')) WHERE p.post_id='" . (int)$getData['pid'] . "' AND p.post_approved='0' LIMIT 0,1");

        return $query->row;
    }

    public function approveTopicAndPost($postData){
        foreach($postData['marked_p_id'] as $ptff_id){
            list($pid,$tid,$fid,$fpid)=explode('|',$ptff_id);

            if($fpid==$pid){
                $this->db->query("UPDATE " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=t.forum_id) LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=t.topic_poster_id) LEFT JOIN " . DB_PREFIX . "setting AS stt ON (stt.`key`='total_topics') LEFT JOIN " . DB_PREFIX . "setting AS stp ON (stp.`key`='total_posts') SET t.topic_approved='1', f.forum_topics=f.forum_topics+1, f.forum_posts=f.forum_posts+1, u.user_topics=u.user_topics+1, u.user_posts=u.user_posts+1, stt.`value`=stt.`value`+1, stp.`value`=stp.`value`+1 WHERE t.topic_id='" . (int)$tid . "'");

                if(isset($postData['notify-poster'])){
                    $this->sendTopicApprovalNotification($tid);
                }

                //add moderator log
                $topicQuery=$this->db->query("SELECT f.forum_id, fn.forum_name, t.topic_title, t.topic_poster_id, t.topic_first_poster_name FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (t.forum_id=f.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE t.topic_id='" . (int)$tid . "' LIMIT 0,1");

                $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='topic_approve', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$topicQuery->row['topic_poster_id'] . "', forum_id='" . (int)$topicQuery->row['forum_id'] . "', topic_id='" . (int)$tid . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($topicQuery->row['topic_title']),$topicQuery->row['topic_poster_id'],$this->db->escape($topicQuery->row['topic_first_poster_name']),$this->db->escape($topicQuery->row['forum_name']),isset($postData['notify-poster'])?'yes':'no')) . "'");
            }else{
                $this->db->query("UPDATE " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=t.forum_id) LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=(SELECT poster_id FROM " . DB_PREFIX . "community_posts WHERE post_id='" . (int)$pid . "')) LEFT JOIN " . DB_PREFIX . "setting AS stp ON (stp.`key`='total_posts') SET t.topic_replies=t.topic_replies+1, f.forum_posts=f.forum_posts+1, u.user_posts=u.user_posts+1, stp.`value`=stp.`value`+1 WHERE t.topic_id='" . (int)$tid . "'");

                if(isset($postData['notify-poster'])){
                    $this->sendPostApprovalNotification($pid);
                }

                //add moderator log
                $postQuery=$this->db->query("SELECT p.post_id, p.post_subject, p.poster_id, p.post_username, t.topic_id, t.topic_title, f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  WHERE p.post_id='" . (int)$pid . "' LIMIT 0,1");

                $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='post_approve', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$postQuery->row['poster_id'] . "', forum_id='" . (int)$postQuery->row['forum_id'] . "', topic_id='" . (int)$postQuery->row['topic_id'] . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($postQuery->row['post_id'],$this->db->escape($postQuery->row['post_subject']),$postQuery->row['poster_id'],$this->db->escape($postQuery->row['post_username']),$this->db->escape($postQuery->row['topic_title']),$this->db->escape($postQuery->row['forum_name']),isset($postData['notify-poster'])?'yes':'no')) . "'");
            }

            $this->db->query("UPDATE " . DB_PREFIX . "community_posts SET post_approved='1' WHERE post_id='" . (int)$pid . "'");

            //clear sql cache
            $this->sql_cache->delete('viewtopic.posttotal.' . md5($tid));
            $this->sql_cache->delete('viewtopic.postids.' . md5($tid));
            $this->sql_cache->delete('viewtopic.posts.' . md5($tid));
            $this->sql_cache->delete('viewtopic.topicinfo.' . md5($tid));
            $this->sql_cache->delete('viewforum.topictotal.' . md5($fid));
            $this->sql_cache->delete('viewforum.topics.' . md5($fid));
        }

        //clear sql cache
        $this->sql_cache->delete('community.foruminfo.');
    }

    public function disapproveTopicAndPost($postData){
        foreach($postData['marked_p_id'] as $ptff_id){
            list($pid,$tid,,$fpid)=explode('|',$ptff_id);

            if($fpid==$pid){
                if(isset($postData['notify-poster'])){
                    $this->sendTopicDisapprovalNotification($tid);
                }

                //add moderator log
                $topicQuery=$this->db->query("SELECT f.forum_id, fn.forum_name, t.topic_title, t.topic_poster_id, t.topic_first_poster_name FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (t.forum_id=f.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  WHERE t.topic_id='" . (int)$tid . "' LIMIT 0,1");

                $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='topic_disapprove', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$topicQuery->row['topic_poster_id'] . "', forum_id='" . (int)$topicQuery->row['forum_id'] . "', topic_id='" . (int)$tid . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($topicQuery->row['topic_title']),$topicQuery->row['topic_poster_id'],$this->db->escape($topicQuery->row['topic_first_poster_name']),$this->db->escape($topicQuery->row['forum_name']),isset($postData['notify-poster'])?'yes':'no')) . "'");

                $this->db->query("DELETE FROM " . DB_PREFIX . "community_topics WHERE topic_id='" . (int)$tid . "'");
            }else{
                if(isset($postData['notify-poster'])){
                    $this->sendPostDisapprovalNotification($pid);
                }

                //add moderator log
                $postQuery=$this->db->query("SELECT p.post_subject, p.poster_id, p.post_username, t.topic_id, t.topic_title, f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  WHERE p.post_id='" . (int)$pid . "' LIMIT 0,1");

                $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='post_disapprove', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$postQuery->row['poster_id'] . "', forum_id='" . (int)$postQuery->row['forum_id'] . "', topic_id='" . (int)$postQuery->row['topic_id'] . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($postQuery->row['post_subject']),$postQuery->row['poster_id'],$this->db->escape($postQuery->row['post_username']),$this->db->escape($postQuery->row['topic_title']),$this->db->escape($postQuery->row['forum_name']),isset($postData['notify-poster'])?'yes':'no')) . "'");
            }

            $this->db->query("DELETE FROM " . DB_PREFIX . "community_posts WHERE post_id='" . (int)$pid . "'");
        }
    }

    public function approveTopics($postData){
        foreach($postData['marked_t_id'] as $tid){
            $this->db->query("UPDATE " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_posts AS p ON (p.post_id=t.topic_first_post_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=t.forum_id) LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=t.topic_poster_id) LEFT JOIN " . DB_PREFIX . "setting AS stt ON (stt.`key`='total_topics') LEFT JOIN " . DB_PREFIX . "setting AS stp ON (stp.`key`='total_posts') SET t.topic_approved='1', p.post_approved='1', f.forum_topics=f.forum_topics+1, f.forum_posts=f.forum_posts+1, u.user_topics=u.user_topics+1, u.user_posts=u.user_posts+1, stt.`value`=stt.`value`+1, stp.`value`=stp.`value`+1 WHERE t.topic_id='" . (int)$tid . "'");

            if(isset($postData['notify-poster'])){
                $this->sendTopicApprovalNotification($tid);
            }

            //add moderator log
            $topicQuery=$this->db->query("SELECT f.forum_id, fn.forum_name, t.topic_title, t.topic_poster_id, t.topic_first_poster_name FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (t.forum_id=f.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  WHERE t.topic_id='" . (int)$tid . "' LIMIT 0,1");

            $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='topic_approve', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$topicQuery->row['topic_poster_id'] . "', forum_id='" . (int)$topicQuery->row['forum_id'] . "', topic_id='" . (int)$tid . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($topicQuery->row['topic_title']),$topicQuery->row['topic_poster_id'],$this->db->escape($topicQuery->row['topic_first_poster_name']),$this->db->escape($topicQuery->row['forum_name']),isset($postData['notify-poster'])?'yes':'no')) . "'");

            //clear sql cache
            $this->sql_cache->delete('viewtopic.posttotal.' . md5($tid));
            $this->sql_cache->delete('viewtopic.postids.' . md5($tid));
            $this->sql_cache->delete('viewtopic.posts.' . md5($tid));
            $this->sql_cache->delete('viewtopic.topicinfo.' . md5($tid));
        }

        //clear sql cache
        if(isset($this->request->get['fid'])){
            $this->sql_cache->delete('viewforum.topictotal.'  . md5($this->request->get['fid']));
            $this->sql_cache->delete('viewforum.topics.' . md5($this->request->get['fid']));
        }else{
            $this->sql_cache->delete('viewforum.topictotal.');
            $this->sql_cache->delete('viewforum.topics.');
        }

        $this->sql_cache->delete('community.foruminfo.');
    }

    public function disapproveTopics($postData){
        foreach($postData['marked_t_id'] as $tid){
            if(isset($postData['notify-poster'])){
                $this->sendTopicDisapprovalNotification($tid);
            }

            //add moderator log
            $topicQuery=$this->db->query("SELECT f.forum_id, fn.forum_name, t.topic_title, t.topic_poster_id, t.topic_first_poster_name FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (t.forum_id=f.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  WHERE t.topic_id='" . (int)$tid . "' LIMIT 0,1");

            $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='topic_disapprove', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$topicQuery->row['topic_poster_id'] . "', forum_id='" . (int)$topicQuery->row['forum_id'] . "', topic_id='" . (int)$tid . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($topicQuery->row['topic_title']),$topicQuery->row['topic_poster_id'],$this->db->escape($topicQuery->row['topic_first_poster_name']),$this->db->escape($topicQuery->row['forum_name']),isset($postData['notify-poster'])?'yes':'no')) . "'");

            $this->db->query("DELETE t, p FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_posts AS p ON (p.post_id=t.topic_first_post_id) WHERE t.topic_id='" . (int)$tid . "'");
        }
    }

    public function approvePosts($postData){
        foreach($postData['marked_p_id'] as $ptid){
            list($pid,$tid)=explode('|',$ptid);

            $this->db->query("UPDATE " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=p.poster_id) LEFT JOIN " . DB_PREFIX . "setting AS stp ON (stp.`key`='total_posts') SET p.post_approved='1', t.topic_replies=t.topic_replies+1, f.forum_posts=f.forum_posts+1, u.user_posts=u.user_posts+1, stp.`value`=stp.`value`+1 WHERE p.post_id='" . (int)$pid . "'");

            if(isset($postData['notify-poster'])){
                $this->sendPostApprovalNotification($pid);
            }

            //add moderator log
            $postQuery=$this->db->query("SELECT p.post_id, p.post_subject, p.poster_id, p.post_username, t.topic_id, t.topic_title, f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  WHERE p.post_id='" . (int)$pid . "' LIMIT 0,1");

            $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='post_approve', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$postQuery->row['poster_id'] . "', forum_id='" . (int)$postQuery->row['forum_id'] . "', topic_id='" . (int)$postQuery->row['topic_id'] . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($postQuery->row['post_id'],$this->db->escape($postQuery->row['post_subject']),$postQuery->row['poster_id'],$this->db->escape($postQuery->row['post_username']),$this->db->escape($postQuery->row['topic_title']),$this->db->escape($postQuery->row['forum_name']),isset($postData['notify-poster'])?'yes':'no')) . "'");

            //clear sql cache
            $this->sql_cache->delete('viewtopic.posttotal.' . md5($tid));
            $this->sql_cache->delete('viewtopic.postids.' . md5($tid));
            $this->sql_cache->delete('viewtopic.posts.' . md5($tid));
        }

        //clear sql cache
        $this->sql_cache->delete('community.foruminfo.');
    }

    public function disapprovePosts($postData){
        foreach($postData['marked_p_id'] as $pid){
            if(isset($postData['notify-poster'])){
                $this->sendPostDisapprovalNotification($pid);
            }

            //add moderator log
            $postQuery=$this->db->query("SELECT p.post_subject, p.poster_id, p.post_username, t.topic_id, t.topic_title, f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  WHERE p.post_id='" . (int)$pid . "' LIMIT 0,1");

            $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='post_disapprove', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$postQuery->row['poster_id'] . "', forum_id='" . (int)$postQuery->row['forum_id'] . "', topic_id='" . (int)$postQuery->row['topic_id'] . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($postQuery->row['post_subject']),$postQuery->row['poster_id'],$this->db->escape($postQuery->row['post_username']),$this->db->escape($postQuery->row['topic_title']),$this->db->escape($postQuery->row['forum_name']),isset($postData['notify-poster'])?'yes':'no')) . "'");

            $this->db->query("DELETE FROM " . DB_PREFIX . "community_posts WHERE post_id='" . (int)$pid . "'");
        }
    }

    private function sendPostApprovalNotification($postId){
        $query=$this->db->query("SELECT p.post_subject, p.forum_id, p.topic_id, u.user_email FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_users AS u ON(u.user_id=p.poster_id) WHERE p.post_id='" . $postId . "'");

        //send mails
        $this->language->load('community/mcp');

        $subject = sprintf($this->language->get('text_post_approval_subject'), $query->row['post_subject']);

        $message = sprintf($this->language->get('text_post_approval_content'), $query->row['post_subject'], $this->config->get('config_name')) . "\n\n";
        $message .= $this->language->get('text_approval_view_post') . "\n";
        $message .= $this->url->link('community/viewtopic', 'fid=' . $query->row['forum_id'] . '&tid=' . $query->row['topic_id'] . '&pid=' . $postId . '#' . $postId, 'SSL') . "\n\n";
        $message .= $this->language->get('text_approval_view_topic') . "\n";
        $message .= $this->url->link('community/viewtopic', 'fid=' . $query->row['forum_id'] . '&tid=' . $query->row['topic_id'], 'SSL') . "\n\n";
        $message .= $this->language->get('text_approval_thanks') . "\n";
        $message .= $this->config->get('config_name') . "\40" . $this->language->get('text_approval_team');

        $mail = new Mail();
        $mail->protocol = $this->config->get('config_mail_protocol');
        $mail->parameter = $this->config->get('config_mail_parameter');
        $mail->hostname = $this->config->get('config_smtp_host');
        $mail->username = $this->config->get('config_smtp_username');
        $mail->password = $this->config->get('config_smtp_password');
        $mail->port = $this->config->get('config_smtp_port');
        $mail->timeout = $this->config->get('config_smtp_timeout');
        $mail->setTo($query->row['user_email']);
        $mail->setFrom($this->config->get('config_community_email'));
        $mail->setSender($this->config->get('config_name') . "\40" . $this->language->get('text_approval_team'));
        $mail->setSubject(html_entity_decode($subject, ENT_QUOTES, 'UTF-8'));
        $mail->setText(html_entity_decode($message, ENT_QUOTES, 'UTF-8'));
        $mail->send();
    }

    private function sendTopicApprovalNotification($topicId){
        $query=$this->db->query("SELECT t.topic_title, t.forum_id, u.user_email FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_users AS u ON(u.user_id=t.topic_poster_id) WHERE t.topic_id='" . $topicId . "'");

        //send mails
        $this->language->load('community/mcp');

        $subject = sprintf($this->language->get('text_topic_approval_subject'), $query->row['topic_title']);

        $message = sprintf($this->language->get('text_topic_approval_content'), $query->row['topic_title'], $this->config->get('config_name')) . "\n\n";
        $message .= $this->language->get('text_approval_view_topic') . "\n";
        $message .= $this->url->link('community/viewtopic', 'fid=' . $query->row['forum_id'] . '&tid=' . $topicId, 'SSL') . "\n\n";
        $message .= $this->language->get('text_approval_thanks') . "\n";
        $message .= $this->config->get('config_name') . "\40" . $this->language->get('text_approval_team');

        $mail = new Mail();
        $mail->protocol = $this->config->get('config_mail_protocol');
        $mail->parameter = $this->config->get('config_mail_parameter');
        $mail->hostname = $this->config->get('config_smtp_host');
        $mail->username = $this->config->get('config_smtp_username');
        $mail->password = $this->config->get('config_smtp_password');
        $mail->port = $this->config->get('config_smtp_port');
        $mail->timeout = $this->config->get('config_smtp_timeout');
        $mail->setTo($query->row['user_email']);
        $mail->setFrom($this->config->get('config_community_email'));
        $mail->setSender($this->config->get('config_name') . "\40" . $this->language->get('text_approval_team'));
        $mail->setSubject(html_entity_decode($subject, ENT_QUOTES, 'UTF-8'));
        $mail->setText(html_entity_decode($message, ENT_QUOTES, 'UTF-8'));
        $mail->send();
    }

    private function sendPostDisapprovalNotification($postId){
        $query=$this->db->query("SELECT p.post_subject, p.forum_id, p.topic_id, u.user_email FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_users AS u ON(u.user_id=p.poster_id) WHERE p.post_id='" . $postId . "'");

        //send mails
        $this->language->load('community/mcp');

        $subject = sprintf($this->language->get('text_post_disapproval_subject'), $query->row['post_subject']);

        $message = sprintf($this->language->get('text_post_disapproval_content'), $query->row['post_subject'], $this->config->get('config_name')) . "\n\n";
        $message .= $this->language->get('text_approval_thanks') . "\n";
        $message .= $this->config->get('config_name') . "\40" . $this->language->get('text_approval_team');

        $mail = new Mail();
        $mail->protocol = $this->config->get('config_mail_protocol');
        $mail->parameter = $this->config->get('config_mail_parameter');
        $mail->hostname = $this->config->get('config_smtp_host');
        $mail->username = $this->config->get('config_smtp_username');
        $mail->password = $this->config->get('config_smtp_password');
        $mail->port = $this->config->get('config_smtp_port');
        $mail->timeout = $this->config->get('config_smtp_timeout');
        $mail->setTo($query->row['user_email']);
        $mail->setFrom($this->config->get('config_community_email'));
        $mail->setSender($this->config->get('config_name') . "\40" . $this->language->get('text_approval_team'));
        $mail->setSubject(html_entity_decode($subject, ENT_QUOTES, 'UTF-8'));
        $mail->setText(html_entity_decode($message, ENT_QUOTES, 'UTF-8'));
        $mail->send();
    }

    private function sendTopicDisapprovalNotification($topicId){
        $query=$this->db->query("SELECT t.topic_title, t.forum_id, u.user_email FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_users AS u ON(u.user_id=t.topic_poster_id) WHERE t.topic_id='" . $topicId . "'");

        //send mails
        $this->language->load('community/mcp');

        $subject = sprintf($this->language->get('text_topic_disapproval_subject'), $query->row['topic_title']);

        $message = sprintf($this->language->get('text_topic_disapproval_content'), $query->row['topic_title'], $this->config->get('config_name')) . "\n\n";
        $message .= $this->language->get('text_approval_thanks') . "\n";
        $message .= $this->config->get('config_name') . "\40" . $this->language->get('text_approval_team');

        $mail = new Mail();
        $mail->protocol = $this->config->get('config_mail_protocol');
        $mail->parameter = $this->config->get('config_mail_parameter');
        $mail->hostname = $this->config->get('config_smtp_host');
        $mail->username = $this->config->get('config_smtp_username');
        $mail->password = $this->config->get('config_smtp_password');
        $mail->port = $this->config->get('config_smtp_port');
        $mail->timeout = $this->config->get('config_smtp_timeout');
        $mail->setTo($query->row['user_email']);
        $mail->setFrom($this->config->get('config_community_email'));
        $mail->setSender($this->config->get('config_name') . "\40" . $this->language->get('text_approval_team'));
        $mail->setSubject(html_entity_decode($subject, ENT_QUOTES, 'UTF-8'));
        $mail->setText(html_entity_decode($message, ENT_QUOTES, 'UTF-8'));
        $mail->send();
    }
    /**************moderation queue end**************/

    /**************reported messages start**************/
    public function getOpenPmReportTotal(){
        $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_reports WHERE report_closed='0' AND post_id='0'");

        return $query->row['total'];
    }

    public function getOpenPmReports($start,$limit){
        $query=$this->db->query("SELECT r.report_id, r.pm_id, r.post_id, r.topic_id, r.report_time, r.user_notify, ru.user_id AS reporter_id, ru.username AS reporter_name, p.message_subject, p.message_time, p.to_address, u.user_id, u.username FROM " . DB_PREFIX . "community_reports AS r LEFT JOIN " . DB_PREFIX . "community_privmsgs AS p ON (p.msg_id=r.pm_id) LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=p.author_id) LEFT JOIN " . DB_PREFIX . "community_users AS ru ON (ru.user_id=r.user_id) WHERE r.report_closed='0' AND r.post_id='0' ORDER BY r.report_time DESC LIMIT " . (int)$start . "," . (int)$limit);
        return $query->rows;
    }

    public function getClosedPmReportTotal(){
        $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_reports WHERE report_closed='1' AND post_id='0'");

        return $query->row['total'];
    }

    public function getClosedPmReports($start,$limit){
        $query=$this->db->query("SELECT r.report_id, r.pm_id, r.post_id, r.topic_id, r.report_time, ru.user_id AS reporter_id, ru.username AS reporter_name, p.message_subject, p.message_time, p.to_address, u.user_id, u.username FROM " . DB_PREFIX . "community_reports AS r LEFT JOIN " . DB_PREFIX . "community_privmsgs AS p ON (p.msg_id=r.pm_id) LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=p.author_id) LEFT JOIN " . DB_PREFIX . "community_users AS ru ON (ru.user_id=r.user_id) WHERE r.report_closed='1' AND r.post_id='0' ORDER BY r.report_time DESC LIMIT " . (int)$start . "," . (int)$limit);
        return $query->rows;
    }

    public function getPmReportDetails($getData){
        $query=$this->db->query("SELECT r.report_id, r.post_id, r.topic_id, r.pm_id, r.report_closed, r.report_time, r.user_notify, ru.user_id AS reporter_id, ru.username AS reporter_name, pr.message_subject, pr.message_text, pr.to_address, pr.message_time, u.user_id, u.username, u.user_type, rr.reason_title, IF(LENGTH(r.report_text)=0, rr.reason_description, r.report_text) AS report_info FROM " . DB_PREFIX . "community_reports AS r LEFT JOIN " . DB_PREFIX . "community_report_reasons AS rr ON (rr.reason_id=r.reason_id) LEFT JOIN " . DB_PREFIX . "community_users AS ru ON(ru.user_id=r.user_id) LEFT JOIN " . DB_PREFIX . "community_privmsgs AS pr ON (pr.msg_id=r.pm_id) LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=pr.author_id) WHERE r.report_id='" . (int)$getData['rid'] . "' LIMIT 0,1");

        return $query->row;
    }

    public function getOpenReportTotal($getData){
        $sql="SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_reports WHERE report_closed='0' AND pm_id='0'";

        if(isset($getData['fid'])){
            $sql.=" AND forum_id='" . (int)$getData['fid'] . "'";
        }

        $query=$this->db->query($sql);

        return $query->row['total'];
    }

    public function getOpenReports($getData,$start,$limit){
        $sql="SELECT r.report_id, r.topic_id, r.post_id, r.pm_id, r.user_notify, r.report_time, ru.user_id AS reporter_id, ru.username AS reporter_name, p.post_id, p.post_subject, p.post_time, f.forum_id, fn.forum_name, u.user_id, u.username FROM " . DB_PREFIX . "community_reports AS r LEFT JOIN " . DB_PREFIX . "community_posts AS p ON (p.post_id=r.post_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=p.poster_id) LEFT JOIN " . DB_PREFIX . "community_users AS ru ON (ru.user_id=r.user_id) WHERE r.report_closed='0' AND r.pm_id='0'";

        if(isset($getData['fid'])){
            $sql.=" AND r.forum_id='" . (int)$getData['fid'] . "'";
        }

        $sql.=" ORDER BY r.report_time DESC LIMIT " . (int)$start . "," .  (int)$limit;

        $query=$this->db->query($sql);

        return $query->rows;
    }

    public function getClosedReportTotal($getData){
        $sql="SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_reports WHERE report_closed='1' AND pm_id='0'";

        if(isset($getData['fid'])){
            $sql.=" AND forum_id='" . (int)$getData['fid'] . "'";
        }

        $query=$this->db->query($sql);

        return $query->row['total'];
    }

    public function getClosedReports($getData,$start,$limit){
        $sql="SELECT r.report_id, r.topic_id, r.post_id, r.pm_id, r.user_notify, r.report_time, ru.user_id AS reporter_id, ru.username AS reporter_name, p.post_id, p.post_subject, p.post_time, f.forum_id, fn.forum_name, u.user_id, u.username FROM " . DB_PREFIX . "community_reports AS r LEFT JOIN " . DB_PREFIX . "community_posts AS p ON (p.post_id=r.post_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=p.poster_id) LEFT JOIN " . DB_PREFIX . "community_users AS ru ON (ru.user_id=r.user_id) WHERE r.report_closed='1' AND r.pm_id='0'";

        if(isset($getData['fid'])){
            $sql.=" AND r.forum_id='" . (int)$getData['fid'] . "'";
        }

        $sql.=" ORDER BY r.report_time DESC LIMIT " . (int)$start . "," .  (int)$limit;

        $query=$this->db->query($sql);

        return $query->rows;
    }

    public function getReportDetails($getData){
        $query=$this->db->query("SELECT r.report_id, r.post_id, r.topic_id, r.forum_id, r.pm_id, r.report_closed, r.report_time, r.user_notify, ru.user_id AS reporter_id, ru.username AS reporter_name, p.post_subject, p.post_text, p.post_time, p.post_deleted, p.enable_bbcode, p.enable_smilies, p.enable_magic_url, u.user_id, u.username, u.user_type, rr.reason_title, IF(LENGTH(r.report_text)=0, rr.reason_description, r.report_text) AS report_info, pe.posting_bbcode, pe.posting_bbcode_url, pe.posting_bbcode_img, pe.posting_bbcode_flash, pe.posting_smiles FROM " . DB_PREFIX . "community_reports AS r LEFT JOIN " . DB_PREFIX . "community_report_reasons AS rr ON (rr.reason_id=r.reason_id) LEFT JOIN " . DB_PREFIX . "community_users AS ru ON(ru.user_id=r.user_id) LEFT JOIN " . DB_PREFIX . "community_posts AS p ON (p.post_id=r.post_id AND p.forum_id=r.forum_id) LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=p.poster_id) LEFT JOIN " . DB_PREFIX . "community_permission AS pe ON (pe.forum_id=p.forum_id AND pe.role_id=u.user_type AND pe.rank_id=IF(u.user_type=7,u.user_rank,'0')) WHERE r.report_id='" . (int)$getData['rid'] . "' LIMIT 0,1");

        return $query->row;
    }

    public function deleteReports($postData){
        foreach($postData['marked_r_id'] as $idstr){
            list($rid,$tid,$pid,$mid,,$reporter_name,$reporter_id,$uid,$username)=explode('|',$idstr);

            //add moderator log
            if($pid){
                $reportQuery=$this->db->query("SELECT r.report_text, rr.reason_title, p.post_subject, f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_reports AS r LEFT JOIN " . DB_PREFIX . "community_report_reasons AS rr ON (rr.reason_id=r.reason_id) LEFT JOIN " . DB_PREFIX . "community_posts AS p ON (p.post_id=r.post_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=r.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  WHERE r.report_id='" . (int)$rid . "' LIMIT 0,1");

                $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='report_delete', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$reporter_id . "', forum_id='" . (int)$reportQuery->row['forum_id'] . "', topic_id='" . (int)$tid . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($reportQuery->row['report_text']), $reportQuery->row['reason_title'], $this->db->escape($reporter_name), isset($postData['delete-post'])?'':$pid, $reportQuery->row['post_subject'], isset($postData['other-actions'])&&$postData['other-actions']=='w'?$uid:'', isset($postData['other-actions'])&&$postData['other-actions']=='w'?$this->db->escape($username):'', isset($postData['other-actions'])&&$postData['other-actions']=='w'?$this->db->escape($postData['warning-details']):'',isset($postData['other-actions'])&&$postData['other-actions']=='b'?$uid:'', isset($postData['other-actions'])&&$postData['other-actions']=='b'?$this->db->escape($username):'', isset($postData['other-actions'])&&$postData['other-actions']=='b'?$this->db->escape($postData['ban-reason']):'', isset($postData['other-actions'])&&$postData['other-actions']=='b'?$this->db->escape($postData['ban-reason-shown']):'')) . "'");
            }else{
                $reportQuery=$this->db->query("SELECT r.report_text, rr.reason_title FROM " . DB_PREFIX . "community_reports AS r LEFT JOIN " . DB_PREFIX . "community_report_reasons AS rr ON (rr.reason_id=r.reason_id) WHERE r.report_id='" . (int)$rid . "' LIMIT 0,1");

                $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='pm_report_delete', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$reporter_id . "', forum_id='0', topic_id='0', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($reportQuery->row['report_text']), $reportQuery->row['reason_title'], $this->db->escape($reporter_name), isset($postData['other-actions'])&&$postData['other-actions']=='w'?$uid:'', isset($postData['other-actions'])&&$postData['other-actions']=='w'?$this->db->escape($username):'', isset($postData['other-actions'])&&$postData['other-actions']=='w'?$this->db->escape($postData['warning-details']):'',isset($postData['other-actions'])&&$postData['other-actions']=='b'?$uid:'', isset($postData['other-actions'])&&$postData['other-actions']=='b'?$this->db->escape($username):'', isset($postData['other-actions'])&&$postData['other-actions']=='b'?$this->db->escape($postData['ban-reason']):'', isset($postData['other-actions'])&&$postData['other-actions']=='b'?$this->db->escape($postData['ban-reason-shown']):'')) . "'");
            }

            $this->db->query("DELETE FROM " . DB_PREFIX . "community_reports WHERE report_id='" . (int)$rid . "'");

            if($pid){// post report
                $this->db->query("UPDATE " . DB_PREFIX . "community_posts SET post_reported='0' WHERE post_id='" . (int)$pid . "'");

                $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_reports WHERE report_closed='0' AND topic_id='" . (int)$tid . "'");

                if(!$query->row['total']){
                    $this->db->query("UPDATE " . DB_PREFIX . "community_topics SET topic_reported='0' WHERE topic_id='" . (int)$tid . "'");
                }

                //if delete post
                if(isset($postData['delete-post'])){
                    $this->deletePostFromReport($postData);
                }
            }else{// pm report
                $this->db->query("UPDATE " . DB_PREFIX . "community_privmsgs SET message_reported='0' WHERE msg_id='" . (int)$mid . "'");
            }

            // in the report detail page
            if(isset($postData['other-actions'])){
                if($postData['other-actions']=='b'){
                    $this->addBanningFromReport($postData);
                }elseif($postData['other-actions']=='w'){
                    $this->addWarningFromReport($postData);
                }
            }
        }
    }

    public function closeReports($postData){
        $this->language->load('community/mcp');

        foreach($postData['marked_r_id'] as $idstr){
            list($rid,$tid,$pid,$mid,$notify,$reporter_name,$reporter_id,$uid,$username)=explode('|',$idstr);

            $this->db->query("UPDATE " . DB_PREFIX . "community_reports SET report_closed='1' WHERE report_id='" . (int)$rid . "'");

            if($pid){//post report
                $this->db->query("UPDATE " . DB_PREFIX . "community_posts SET post_reported='0' WHERE post_id='" . (int)$pid . "'");

                $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_reports WHERE report_closed='0' AND topic_id='" . (int)$tid . "'");

                if(!$query->row['total']){
                    $this->db->query("UPDATE " . DB_PREFIX . "community_topics SET topic_reported='0' WHERE topic_id='" . (int)$tid . "'");
                }

                if($notify){
                    //send pm
                    $postQuery=$this->db->query("SELECT post_subject FROM " . DB_PREFIX . "community_posts WHERE post_id='" . (int)$pid . "'");

                    $to_address=serialize(array($reporter_name=>$reporter_id));

                    $this->db->query("INSERT INTO " . DB_PREFIX . "community_privmsgs SET author_id='" . (int)$this->customer->getForumUserId() . "', message_time='" . (int)time() . "', message_subject='" . $this->language->get('text_report_dealt_message_subject') . "', message_text='" . sprintf($this->language->get('text_report_dealt_message_content'),$postQuery->row['post_subject'],$this->url->link('community/profile','uid=' . $uid),$username) . "', to_address='" . $to_address . "'");

                    $this->db->query("INSERT INTO " . DB_PREFIX . "community_privmsgs_to SET msg_id='" . (int)$this->db->getLastId() . "', user_id='" . (int)$reporter_id . "', author_id='" . (int)$this->customer->getForumUserId() . "'");

                    $this->db->query("UPDATE " . DB_PREFIX . "community_users SET user_new_privmsg=user_new_privmsg+1 WHERE user_id='" . (int)$reporter_id . "'");
                }

                //add moderator log
                $reportQuery=$this->db->query("SELECT r.report_text, rr.reason_title, p.post_subject, f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_reports AS r LEFT JOIN " . DB_PREFIX . "community_report_reasons AS rr ON (rr.reason_id=r.reason_id) LEFT JOIN " . DB_PREFIX . "community_posts AS p ON (p.post_id=r.post_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=r.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  WHERE r.report_id='" . (int)$rid . "' LIMIT 0,1");

                $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='report_close', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$reporter_id . "', forum_id='" . (int)$reportQuery->row['forum_id'] . "', topic_id='" . (int)$tid . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($reportQuery->row['report_text']), $reportQuery->row['reason_title'], $this->db->escape($reporter_name), isset($postData['delete-post'])?'':$pid, $reportQuery->row['post_subject'], isset($postData['other-actions'])&&$postData['other-actions']=='w'?$uid:'', isset($postData['other-actions'])&&$postData['other-actions']=='w'?$this->db->escape($username):'', isset($postData['other-actions'])&&$postData['other-actions']=='w'?$this->db->escape($postData['warning-details']):'',isset($postData['other-actions'])&&$postData['other-actions']=='b'?$uid:'', isset($postData['other-actions'])&&$postData['other-actions']=='b'?$this->db->escape($username):'', isset($postData['other-actions'])&&$postData['other-actions']=='b'?$this->db->escape($postData['ban-reason']):'', isset($postData['other-actions'])&&$postData['other-actions']=='b'?$this->db->escape($postData['ban-reason-shown']):'',$notify?'yes':'no')) . "'");

                //if delete post
                if(isset($postData['delete-post'])){
                    $this->deletePostFromReport($postData);
                }
            }else{//pm report
                $this->db->query("UPDATE " . DB_PREFIX . "community_privmsgs SET message_reported='0' WHERE msg_id='" . (int)$mid . "'");

                if($notify){
                    //send pm
                    $to_address=serialize(array($reporter_name=>$reporter_id));

                    $this->db->query("INSERT INTO " . DB_PREFIX . "community_privmsgs SET author_id='" . (int)$this->customer->getForumUserId() . "', message_time='" . (int)time() . "', message_subject='" . $this->language->get('text_pm_report_dealt_message_subject') . "', message_text='" . sprintf($this->language->get('text_pm_report_dealt_message_content'),$this->url->link('community/profile','uid=' . $uid),$username) . "', to_address='" . $to_address . "'");

                    $this->db->query("INSERT INTO " . DB_PREFIX . "community_privmsgs_to SET msg_id='" . (int)$this->db->getLastId() . "', user_id='" . (int)$reporter_id . "', author_id='" . (int)$this->customer->getForumUserId() . "'");

                    if($this->customer->getForumUserId()!=$reporter_id){
                        $this->db->query("UPDATE " . DB_PREFIX . "community_users SET user_new_privmsg=user_new_privmsg+1 WHERE user_id='" . (int)$reporter_id . "'");
                    }
                }

                //add moderator log
                $reportQuery=$this->db->query("SELECT r.report_text, rr.reason_title FROM " . DB_PREFIX . "community_reports AS r LEFT JOIN " . DB_PREFIX . "community_report_reasons AS rr ON (rr.reason_id=r.reason_id) WHERE r.report_id='" . (int)$rid . "' LIMIT 0,1");

                $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='pm_report_close', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$reporter_id . "', forum_id='0', topic_id='0', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($reportQuery->row['report_text']), $reportQuery->row['reason_title'], $this->db->escape($reporter_name), isset($postData['other-actions'])&&$postData['other-actions']=='w'?$uid:'', isset($postData['other-actions'])&&$postData['other-actions']=='w'?$this->db->escape($username):'', isset($postData['other-actions'])&&$postData['other-actions']=='w'?$this->db->escape($postData['warning-details']):'',isset($postData['other-actions'])&&$postData['other-actions']=='b'?$uid:'', isset($postData['other-actions'])&&$postData['other-actions']=='b'?$this->db->escape($username):'', isset($postData['other-actions'])&&$postData['other-actions']=='b'?$this->db->escape($postData['ban-reason']):'', isset($postData['other-actions'])&&$postData['other-actions']=='b'?$this->db->escape($postData['ban-reason-shown']):'',$notify?'yes':'no')) . "'");
            }

            // in the report detail page
            if(isset($postData['other-actions'])){
                if($postData['other-actions']=='b'){
                    $this->addBanningFromReport($postData);
                }elseif($postData['other-actions']=='w'){
                    $this->addWarningFromReport($postData);
                }
            }
        }
    }

    private function addWarningFromReport($postData){
        $this->language->load('community/mcp');

        list(,,$pid,,,,,$uid,$username)=explode('|',$postData['marked_r_id'][0]);

        $this->db->query("INSERT INTO " . DB_PREFIX . "community_warning SET user_id='" . (int)$uid . "', post_id='" . (int)$pid . "', warning_time='" . (int)time() . "'");

        $this->db->query("UPDATE " . DB_PREFIX . "community_users SET user_warnings=user_warnings+1, user_last_warning='" . (int)time() . "', user_new_privmsg=user_new_privmsg+1 WHERE user_id='" . (int)$uid . "'");

        //send pm
        $to_address=serialize(array($username=>$uid));

        $this->db->query("INSERT INTO " . DB_PREFIX . "community_privmsgs SET author_id='" . (int)$this->customer->getForumUserId() . "', message_time='" . (int)time() . "', message_subject='" . $this->language->get('text_warning_message_subject') . "', message_text='" . $this->db->escape($postData['warning-details']) . "', to_address='" . $to_address . "'");

        $this->db->query("INSERT INTO " . DB_PREFIX . "community_privmsgs_to SET msg_id='" . (int)$this->db->getLastId() . "', user_id='" . (int)$uid . "', author_id='" . (int)$this->customer->getForumUserId() . "'");
    }

    private function addBanningFromReport($postData){
        list(,,,,,,,$uid)=explode('|',$postData['marked_r_id'][0]);

        $this->db->query("SELECT ban_id FROM " . DB_PREFIX . "community_banlist WHERE ban_userid='" . $uid . "'");

        switch($postData['ban-length']){
            case 'p':
                $ban_time_length=0;
                break;
            case '30m':
                $ban_time_length=time()+60*30;
                break;
            case '1h':
                $ban_time_length=time()+60*60;
                break;
            case '6h':
                $ban_time_length=time()+60*60*6;
                break;
            case '1d':
                $ban_time_length=time()+60*60*24;
                break;
            case '7d':
                $ban_time_length=time()+60*60*24*7;
                break;
            case '2w':
                $ban_time_length=time()+60*60*24*14;
                break;
            case '1m':
                $ban_time_length=time()+60*60*24*30;
                break;
            case 'u':
                $ban_time_length=strtotime($postData['ban-date']);
                break;
        }

        if($this->db->countAffected()){
            $this->db->query("UPDATE " . DB_PREFIX . "community_banlist SET ban_end_time='" . (int)$ban_time_length . "', ban_reason='" . $this->db->escape($postData['ban-reason']) . "', ban_give_reason='" . $this->db->escape($postData['ban-reason-shown']) . "' WHERE ban_userid='" . (int)$uid . "'");
        }else{
            $this->db->query("INSERT INTO " . DB_PREFIX . "community_banlist SET ban_userid='" . (int)$uid . "', ban_end_time='" . (int)$ban_time_length . "', ban_reason='" . $this->db->escape($postData['ban-reason']) . "', ban_give_reason='" . $this->db->escape($postData['ban-reason-shown']) . "'");
        }
    }

    private function deletePostFromReport($postData){
        list(,$tid,$pid)=explode('|',$postData['marked_r_id'][0]);

        $topicQuery=$this->db->query("SELECT topic_first_post_id, topic_last_post_id, forum_id FROM " . DB_PREFIX . "community_topics WHERE topic_id='" . (int)$tid . "' LIMIT 0,1");

        $topic_first_post_id=$topicQuery->row['topic_first_post_id'];
        $topic_last_post_id=$topicQuery->row['topic_last_post_id'];

        if($topic_first_post_id==$pid){//if delete the first post, delete the whole topic
            $query=$this->db->query("SELECT post_id, poster_id FROM " . DB_PREFIX . "community_posts WHERE topic_id='" . (int)$tid . "' AND post_deleted='0' ORDER BY post_time DESC");

            $i=0;
            $post_id_str='';

            foreach($query->rows as $result){
                $this->db->query("UPDATE " . DB_PREFIX . "community_users SET user_posts=user_posts-1 WHERE user_id='" . (int)$result['poster_id'] . "'");

                $post_id_str.=$result['post_id'] . ',';

                $i++;
            }

            $post_id_str=rtrim($post_id_str,',');

            $this->db->query("UPDATE " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_posts AS p ON (p.post_id IN (" . $post_id_str . ")) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=t.forum_id) LEFT JOIN " . DB_PREFIX . "community_users AS tu ON (tu.user_id=t.topic_poster_id) LEFT JOIN " . DB_PREFIX . "community_poll_options AS po ON (po.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_poll_votes AS pv ON (pv.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_topic_notify AS tn ON (tn.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_bookmarks AS b ON (b.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "setting AS stt ON (stt.`key`='total_topics') LEFT JOIN " . DB_PREFIX . "setting AS stp ON (stp.`key`='total_posts') SET p.post_deleted='1', t.topic_deleted='1', f.forum_topics=f.forum_topics-1, f.forum_posts=f.forum_posts-" . (int)$i . ", tu.user_topics=tu.user_topics-1, po.topic_deleted='1', pv.topic_deleted='1', tn.topic_deleted='1', b.topic_deleted='1', stt.`value`=stt.`value`-1, stp.`value`=stp.`value`-" . (int)$i . " WHERE t.topic_id='" . (int)$tid . "'");

            //add moderator log
            $topicQuery=$this->db->query("SELECT f.forum_id, fn.forum_name, t.topic_title, t.topic_poster_id, t.topic_first_poster_name FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (t.forum_id=f.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  WHERE t.topic_id='" . (int)$tid . "' LIMIT 0,1");

            $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='topic_delete', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$topicQuery->row['topic_poster_id'] . "', forum_id='" . (int)$topicQuery->row['forum_id'] . "', topic_id='" . (int)$tid . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($topicQuery->row['topic_title']),$topicQuery->row['topic_poster_id'],$this->db->escape($topicQuery->row['topic_first_poster_name']),$this->db->escape($topicQuery->row['forum_name']))) . "'");

            //clear sql cache
            $this->sql_cache->delete('viewtopic.posttotal.' . md5($tid));
            $this->sql_cache->delete('viewtopic.postids.' . md5($tid));
            $this->sql_cache->delete('viewtopic.topicinfo.' . md5($tid));
            $this->sql_cache->delete('viewtopic.posts.' . md5($tid));
            $this->sql_cache->delete('viewforum.topics.' . md5($topicQuery->row['forum_id']));
            $this->sql_cache->delete('viewforum.topictotal.' . md5($topicQuery->row['forum_id']));
            $this->sql_cache->delete('viewforum.announcementtopics.');
            $this->sql_cache->delete('community.foruminfo.');

            return TRUE;
        }elseif($topic_last_post_id==$pid){//if last post in a topic is deleted
            $this->db->query("UPDATE " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=p.poster_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "setting AS s ON (s.`key`='total_posts') SET p.post_deleted='1', u.user_posts=u.user_posts-1, f.forum_posts=f.forum_posts-1, t.topic_replies=t.topic_replies-1, s.`value`=s.`value`-1 WHERE p.post_id='" . (int)$pid . "'");

            $query=$this->db->query("SELECT post_id, poster_id, post_time, post_username, post_subject FROM " . DB_PREFIX . "community_posts WHERE topic_id=" . (int)$tid . " AND post_approved='1' AND post_deleted='0' ORDER BY post_time DESC LIMIT 0,1 ");

            $postLastOneData=$query->row;

            $this->db->query("UPDATE " . DB_PREFIX . "community_topics SET topic_last_post_id='" . (int)$postLastOneData['post_id'] . "', topic_last_poster_id='" . (int)$postLastOneData['poster_id'] . "', topic_last_poster_name='" . $this->db->escape($postLastOneData['post_username']) . "', topic_last_post_subject='" . $this->db->escape($postLastOneData['post_subject']) . "', topic_last_post_time='" . $this->db->escape($postLastOneData['post_time']) . "' WHERE topic_id='" . (int)$tid . "'");

            //add moderator log
            $postQuery=$this->db->query("SELECT p.post_subject, p.poster_id, p.post_username, t.topic_id, t.topic_title, f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  WHERE p.post_id='" . (int)$pid . "' LIMIT 0,1");

            $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='post_delete', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$postQuery->row['poster_id'] . "', forum_id='" . (int)$postQuery->row['forum_id'] . "', topic_id='" . (int)$postQuery->row['topic_id'] . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($postQuery->row['post_subject']),$postQuery->row['poster_id'],$this->db->escape($postQuery->row['post_username']),$this->db->escape($postQuery->row['topic_title']),$this->db->escape($postQuery->row['forum_name']))) . "'");

            //clear viewtopic sql cache
            $this->sql_cache->delete('viewtopic.posttotal.' . md5($tid));
            $this->sql_cache->delete('viewtopic.postids.' . md5($tid));
            $this->sql_cache->delete('viewtopic.posts.' . md5($tid));
            $this->sql_cache->delete('viewtopic.topicinfo.' . md5($tid));
            $this->sql_cache->delete('viewforum.topics.' . md5($topicQuery->row['forum_id']));
            $this->sql_cache->delete('community.foruminfo.');

            return FALSE;
        }else{
            $this->db->query("UPDATE " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=p.poster_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "setting AS s ON (s.`key`='total_posts') SET p.post_deleted='1', u.user_posts=u.user_posts-1, f.forum_posts=f.forum_posts-1, t.topic_replies=t.topic_replies-1, s.`value`=s.`value`-1 WHERE p.post_id='" . (int)$pid . "'");

            //add moderator log
            $postQuery=$this->db->query("SELECT p.post_subject, p.poster_id, p.post_username, t.topic_id, t.topic_title, f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "') WHERE p.post_id='" . (int)$pid . "' LIMIT 0,1");

            $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='post_delete', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$postQuery->row['poster_id'] . "', forum_id='" . (int)$postQuery->row['forum_id'] . "', topic_id='" . (int)$postQuery->row['topic_id'] . "', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($postQuery->row['post_subject']),$postQuery->row['poster_id'],$this->db->escape($postQuery->row['post_username']),$this->db->escape($postQuery->row['topic_title']),$this->db->escape($postQuery->row['forum_name']))) . "'");

            //clear sql cache
            $this->sql_cache->delete('viewtopic.posttotal.' . md5($tid));
            $this->sql_cache->delete('viewtopic.postids.' . md5($tid));
            $this->sql_cache->delete('viewtopic.posts.' . md5($tid));
            $this->sql_cache->delete('viewtopic.topicinfo.' . md5($tid));
            $this->sql_cache->delete('viewforum.topics.' . md5($topicQuery->row['forum_id']));
            $this->sql_cache->delete('community.foruminfo.');

            return FALSE;
        }
    }

    /**************reported messages end**************/

    /**************warning start**************/
    public function getWarnedUserTotal(){
        $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_users WHERE user_warnings>'0'");

        return $query->row['total'];
    }

    public function getWarnedUsers($start,$limit){
        $query=$this->db->query("SELECT user_id, username, user_warnings, user_last_warning FROM " . DB_PREFIX . "community_users WHERE user_warnings>'0' ORDER BY user_last_warning DESC LIMIT " . $start . "," . $limit);

        return $query->rows;
    }

    public function addWarning($postData){
        $this->language->load('community/mcp');

        $username_text=nl2br($postData['warning-usernames']);
        $temp01_usernames=explode('<br />',$username_text);

        $temp02_usernames=array();

        foreach($temp01_usernames as $temp01_username){
            $temp01_username=trim(trim($temp01_username),'<br />');//remove things like \n,\t

            if(!empty($temp01_username)){
                array_push($temp02_usernames,$temp01_username);
            }
        }

        //remove duplicated entities
        $usernames=array_unique($temp02_usernames);

        foreach($usernames as $username){
            $query=$this->db->query("SELECT user_id FROM " . DB_PREFIX . "community_users WHERE username='" . $this->db->escape($username) . "' LIMIT 0,1");

            $this->db->query("INSERT INTO " . DB_PREFIX . "community_warning SET user_id='" . (int)$query->row['user_id'] . "', post_id='0', warning_time='" . (int)time() . "'");

            $this->db->query("UPDATE " . DB_PREFIX . "community_users SET user_warnings=user_warnings+1, user_last_warning='" . (int)time() . "', user_new_privmsg=user_new_privmsg+1 WHERE user_id='" . (int)$query->row['user_id'] . "'");

            //send pm
            $to_address=serialize(array($username=>$query->row['user_id']));

            $this->db->query("INSERT INTO " . DB_PREFIX . "community_privmsgs SET author_id='" . (int)$this->customer->getForumUserId() . "', message_time='" . (int)time() . "', message_subject='" . $this->language->get('text_warning_message_subject') . "', message_text='" . $this->db->escape($postData['warning-details']) . "', to_address='" . $to_address . "'");

            $this->db->query("INSERT INTO " . DB_PREFIX . "community_privmsgs_to SET msg_id='" . (int)$this->db->getLastId() . "', user_id='" . (int)$query->row['user_id'] . "', author_id='" . (int)$this->customer->getForumUserId() . "'");

            //add moderator log
            $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='user_warn', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$query->row['user_id'] . "', forum_id='0', topic_id='0', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($username,$this->db->escape($postData['warning-details']))) . "'");
        }
    }
    /**************warning end**************/

    /**************moderator log start**************/
    public function getForumLogTotal($getData){
        $sql="SELECT COUNT(log_id) AS total FROM " . DB_PREFIX . "community_log";

        if(isset($getData['fid'])){
            $sql.=" WHERE forum_id='" . (int)$getData['fid'] . "'OR forum_id='0'";
        }

        $query=$this->db->query($sql);

        return $query->row['total'];
    }

    public function getForumLogs($getData,$start,$limit){
        $sql="SELECT l.*, u.username FROM " . DB_PREFIX . "community_log AS l LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=l.operator_id)";

        if(isset($getData['fid'])){
            $sql.=" WHERE l.forum_id='" . (int)$getData['fid'] . "' OR l.forum_id='0'";
        }
        $sql.=" ORDER BY l.log_id DESC LIMIT " . $start . "," . $limit;

        $query=$this->db->query($sql);

        return $query->rows;
    }

    public function getForumLogSearchResultTotal($getData){
        $sql="SELECT COUNT(log_id) AS total FROM " . DB_PREFIX . "community_log WHERE";

        if(isset($getData['fid'])){
            $sql.=" (forum_id='" . (int)$getData['fid'] . "'OR forum_id='0') AND";
        }

        $sql.=" (LCASE(log_data) LIKE '%" . $this->db->escape(utf8_strtolower($getData['kw'])) . "%' OR  LCASE(log_type) LIKE '%" . $this->db->escape(utf8_strtolower($getData['kw'])) . "%')";

        $query=$this->db->query($sql);

        return $query->row['total'];
    }

    public function getForumLogSearchResult($getData,$start,$limit){
        $sql="SELECT l.*, u.username FROM " . DB_PREFIX . "community_log AS l LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=l.operator_id) WHERE";

        if(isset($getData['fid'])){
            $sql.=" (l.forum_id='" . (int)$getData['fid'] . "' OR l.forum_id='0') AND";
        }

        $sql.=" (LCASE(l.log_data) LIKE '%" . $this->db->escape(utf8_strtolower($getData['kw'])) . "%' OR  LCASE(l.log_type) LIKE '%" . $this->db->escape(utf8_strtolower($getData['kw'])) . "%')";

        $sql.=" ORDER BY l.log_id DESC LIMIT " . $start . "," . $limit;

        $query=$this->db->query($sql);

        return $query->rows;
    }

    public function deleteAllForumLogs($getData){
        $sql="DELETE FROM " . DB_PREFIX . "community_log";

        if($getData['fid']){
            $sql.=" WHERE forum_id='" . $getData['fid'] . "'";
        }

        $sql.=" LIMIT 1000";

        do{
            $this->db->query($sql);

            sleep(2);
        }while($this->db->countAffected());
    }

    public function deleteForumLogs($getData,$postData){
        $log_id_str='';

        foreach($postData['marked_l_id'] as $log_id){
            $log_id_str.=$log_id . ',';
        }

        $log_id_str=rtrim($log_id_str,',');

        $sql="DELETE FROM " . DB_PREFIX . "community_log WHERE";

        if($getData['fid']){
            $sql.=" forum_id='" . $getData['fid'] . "' AND";
        }

        $sql.=" log_id IN(" . $log_id_str . ")";

        if(!empty($log_id_str)){
            $this->db->query($sql);
        }
    }
    /**************moderator log end**************/

    /**************banning start**************/
    public function BanUsers($postData){
        switch($postData['ban-length']){
            case 'p':
                $ban_time_length=0;
                break;
            case '30m':
                $ban_time_length=time()+60*30;
                break;
            case '1h':
                $ban_time_length=time()+60*60;
                break;
            case '6h':
                $ban_time_length=time()+60*60*6;
                break;
            case '1d':
                $ban_time_length=time()+60*60*24;
                break;
            case '7d':
                $ban_time_length=time()+60*60*24*7;
                break;
            case '2w':
                $ban_time_length=time()+60*60*24*14;
                break;
            case '1m':
                $ban_time_length=time()+60*60*24*30;
                break;
            case 'u':
                $ban_time_length=strtotime($postData['ban-date']);
                break;
        }

        //process ban usernames
        $ban_username_text=nl2br($postData['ban-usernames']);
        $temp01_ban_usernames=explode('<br />',$ban_username_text);

        $temp02_ban_usernames=array();

        foreach($temp01_ban_usernames as $temp01_ban_username){
            $temp01_ban_username=trim(trim($temp01_ban_username),'<br />');//remove things like \n,\t

            if(!empty($temp01_ban_username)){
                array_push($temp02_ban_usernames,$temp01_ban_username);
            }
        }

        //remove duplicated entities
        $ban_usernames=array_unique($temp02_ban_usernames);

        //add or update ban record
        foreach($ban_usernames as $ban_username){
            $userQuery=$this->db->query("SELECT u.user_id, b.ban_id FROM " . DB_PREFIX . "community_users AS u LEFT JOIN " . DB_PREFIX . "community_banlist AS b ON (b.ban_userid=u.user_id) WHERE u.username='" . $this->db->escape($ban_username) . "' LIMIT 0,1");

            if($userQuery->row['ban_id']){
                $this->db->query("UPDATE " . DB_PREFIX . "community_banlist SET ban_end_time='" . (int)$ban_time_length . "', ban_reason='" . $this->db->escape($postData['ban-reason']) . "', ban_give_reason='" . $this->db->escape($postData['ban-reason-shown']) . "' WHERE ban_userid='" . (int)$userQuery->row['user_id'] . "'");
            }else{
                $this->db->query("INSERT INTO " . DB_PREFIX . "community_banlist SET ban_userid='" . (int)$userQuery->row['user_id'] . "', ban_end_time='" . (int)$ban_time_length . "', ban_reason='" . $this->db->escape($postData['ban-reason']) . "', ban_give_reason='" . $this->db->escape($postData['ban-reason-shown']) . "'");
            }

            //add moderator log
            $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='user_ban', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$userQuery->row['user_id'] . "', forum_id='0', topic_id='0', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($ban_username,$ban_time_length,$this->db->escape($postData['ban-reason']),$this->db->escape($postData['ban-reason-shown']))) . "'");
        }
    }

    public function validateUsers($userPostData){
        $username_text=nl2br($userPostData);
        $temp01_usernames=explode('<br />',$username_text);

        $temp02_usernames=array();

        foreach($temp01_usernames as $temp01_username){
            $temp01_username=trim(trim($temp01_username),'<br />');//remove things like \n,\t

            if(!empty($temp01_username)){
                array_push($temp02_usernames,$temp01_username);
            }
        }

        //remove duplicated entities
        $usernames=array_unique($temp02_usernames);

        foreach($usernames as $username){
            $query=$this->db->query("SELECT user_type FROM " . DB_PREFIX . "community_users WHERE username='" . $this->db->escape($username) . "' LIMIT 0,1");

            if(!$query->row){
                return 'empty';
            }elseif($query->row['user_type']<=3){
                return 'mod';
            }
        }

        return FALSE;
    }

    public function BanIps($postData){
        switch($postData['ban-length']){
            case 'p':
                $ban_time_length=0;
                break;
            case '30m':
                $ban_time_length=time()+60*30;
                break;
            case '1h':
                $ban_time_length=time()+60*60;
                break;
            case '6h':
                $ban_time_length=time()+60*60*6;
                break;
            case '1d':
                $ban_time_length=time()+60*60*24;
                break;
            case '7d':
                $ban_time_length=time()+60*60*24*7;
                break;
            case '2w':
                $ban_time_length=time()+60*60*24*14;
                break;
            case '1m':
                $ban_time_length=time()+60*60*24*30;
                break;
            case 'u':
                $ban_time_length=strtotime($postData['ban-date']);
                break;
        }

        //process ban ips
        $ban_ip_text=nl2br($postData['ban-ips']);
        $temp01_ban_ips=explode('<br />',$ban_ip_text);

        $temp02_ban_ips=array();

        foreach($temp01_ban_ips as $temp01_ban_ip){
            $temp01_ban_ip=trim(trim($temp01_ban_ip),'<br />');//remove things like \n,\t

            if(!empty($temp01_ban_ip)){
                array_push($temp02_ban_ips,$temp01_ban_ip);
            }
        }

        //remove duplicated entities
        $ban_ips=array_unique($temp02_ban_ips);

        //add or update ban record
        foreach($ban_ips as $ban_ip){
            $ip_arr=explode('-',$ban_ip);

            if(count($ip_arr)>1){
                $ban_ip_start=sprintf("%u", ip2long($ip_arr[0]));
                $ban_ip_end=sprintf("%u", ip2long($ip_arr[1]));
            }else{
                $ban_ip_start=0;
                $ban_ip_end=sprintf("%u", ip2long($ip_arr[0]));
            }

            //DO NOT USE (int) below to convert ban_ip string to int!!!!!! 'cause ip2long('ip') might outrange the range of int
            $this->db->query("SELECT ban_id FROM " . DB_PREFIX . "community_banlist WHERE ban_ip_start='" . $ban_ip_start . "' AND ban_ip_end='" . $ban_ip_end . "'");

            if(!$this->db->countAffected()){
                $this->db->query("INSERT INTO " . DB_PREFIX . "community_banlist SET ban_ip_start='" . $ban_ip_start . "', ban_ip_end='" . $ban_ip_end . "', ban_end_time='" . (int)$ban_time_length . "', ban_reason='" . $this->db->escape($postData['ban-reason']) . "', ban_give_reason='" . $this->db->escape($postData['ban-reason-shown']) . "'");

                //add moderator log
                $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='ip_ban', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='0', forum_id='0', topic_id='0', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($this->db->escape($ban_ip),$ban_time_length,$this->db->escape($postData['ban-reason']),$this->db->escape($postData['ban-reason-shown']))) . "'");
            }
        }
    }

    public function getBannedUserTotal(){
        $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_banlist WHERE ban_ip_start='0' AND ban_ip_end='0'");

        return $query->row['total'];
    }

    public function getBannedUsers($start,$limit){
        $query=$this->db->query("SELECT b.ban_id, b.ban_end_time, b.ban_reason, b.ban_give_reason, u.username, u.user_id FROM " . DB_PREFIX . "community_banlist AS b LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=b.ban_userid) WHERE b.ban_ip_start='0' AND b.ban_ip_end='0' LIMIT " . $start . "," . $limit);

        return $query->rows;
    }

    public function getBannedIpTotal(){
        $query=$this->db->query("SELECT COUNT(*) AS total FROM " . DB_PREFIX . "community_banlist WHERE ban_userid='0'");

        return $query->row['total'];
    }

    public function getBannedIps($start,$limit){
        $query=$this->db->query("SELECT ban_id, ban_ip_start, ban_ip_end, ban_end_time, ban_reason, ban_give_reason FROM " . DB_PREFIX . "community_banlist WHERE ban_userid='0' LIMIT " . $start . "," . $limit);

        return $query->rows;
    }

    public function unban($postData){
        foreach($postData['marked_b_id'] as $bid){
            //add moderator log
            $query=$this->db->query("SELECT b.ban_userid, b.ban_ip_start, b.ban_ip_end, u.username FROM " . DB_PREFIX . "community_banlist AS b LEFT JOIN " . DB_PREFIX . "community_users AS u ON (u.user_id=b.ban_userid) WHERE b.ban_id='" . (int)$bid . "'LIMIT 0,1");

            if($query->row['ban_userid']){
                $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='user_unban', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='" . (int)$query->row['ban_userid'] . "', forum_id='0', topic_id='0', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($query->row['username'])) . "'");
            }else{
                if($query->row['ban_ip_start']){
                    $ip_str=long2ip($query->row['ban_ip_start']) . '-' . long2ip($query->row['ban_ip_end']);
                }else{
                    $ip_str=long2ip($query->row['ban_ip_end']);
                }
                $this->db->query("INSERT INTO " . DB_PREFIX . "community_log SET log_type='ip_unban', operator_id='" . (int)$this->customer->getForumUserId() . "', user_id='0', forum_id='0', topic_id='0', log_ip='" . $this->db->escape($this->request->server['REMOTE_ADDR']) . "', log_time='" . (int)time() . "', log_data='" . serialize(array($ip_str)) . "'");
            }

            $this->db->query("DELETE FROM " . DB_PREFIX . "community_banlist WHERE ban_id='" . (int)$bid . "'");
        }
    }
    /**************banning end**************/

    /**************deleted topics and posts start**************/
    public function getDeletedTopicTotal($getData){
        $sql="SELECT COUNT(topic_id) AS total FROM " . DB_PREFIX . "community_topics WHERE topic_deleted='1'";

        if(isset($getData['fid'])){
            $sql.=" AND forum_id='" . (int)$getData['fid'] . "'";
        }

        $query=$this->db->query($sql);

        return $query->row['total'];
    }

    public function getDeletedTopics($getData,$start,$limit){
        $sql="SELECT t.topic_id, t.topic_title, t.topic_time, t.topic_first_post_id, t.topic_first_poster_name, f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=t.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  WHERE t.topic_deleted='1'";

        if(isset($getData['fid'])){
            $sql.=" AND t.forum_id='" . (int)$getData['fid'] . "'";
        }

        $sql.=" ORDER BY t.topic_time DESC LIMIT " . $start . "," . $limit;

        $query=$this->db->query($sql);

        return $query->rows;
    }

    public function restoreDeletedTopics($postData){
        foreach($postData['marked_t_id'] as $tid){
            $query=$this->db->query("SELECT poster_id FROM " . DB_PREFIX . "community_posts WHERE topic_id='" . (int)$tid . "'");

            $i=0;

            foreach($query->rows as $postResult){
                $this->db->query("UPDATE " . DB_PREFIX . "community_users SET user_posts=user_posts+1 WHERE user_id='" . (int)$postResult['poster_id'] . "'");

                $i++;
            }

            $this->db->query("UPDATE " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_posts AS p ON (p.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=t.forum_id) LEFT JOIN " . DB_PREFIX . "community_users AS tu ON (tu.user_id=t.topic_poster_id) LEFT JOIN " . DB_PREFIX . "community_poll_options AS po ON (po.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_poll_votes AS pv ON (pv.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_topic_notify AS tn ON (tn.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_bookmarks AS b ON (b.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "setting AS stt ON (stt.`key`='total_topics') LEFT JOIN " . DB_PREFIX . "setting AS stp ON (stp.`key`='total_posts') SET p.post_deleted='0', t.topic_deleted='0', f.forum_topics=f.forum_topics+1, f.forum_posts=f.forum_posts+" . (int)$i . ", tu.user_topics=tu.user_topics+1, po.topic_deleted='0', pv.topic_deleted='0', tn.topic_deleted='0', b.topic_deleted='0', stt.`value`=stt.`value`+1, stp.`value`=stp.`value`+" . (int)$i . " WHERE t.topic_id='" . (int)$tid . "'");

            //clear sql cache
            $encrypt_data=md5($tid);
            $this->sql_cache->delete('viewtopic.posttotal.' . $encrypt_data);
            $this->sql_cache->delete('viewtopic.postids.' . $encrypt_data);
            $this->sql_cache->delete('viewtopic.topicinfo.' . $encrypt_data);
            $this->sql_cache->delete('viewtopic.posts.' . $encrypt_data);
        }

        //clear sql cache
        $this->sql_cache->delete('viewforum.topics.');
        $this->sql_cache->delete('viewforum.topictotal.');
        $this->sql_cache->delete('viewforum.announcementtopics.');
        $this->sql_cache->delete('community.foruminfo.');
    }

    public function clearAllDeletedTopics($getData){
        $sql="DELETE t, p, po, pv, tn, b FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_posts AS p ON (p.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_poll_options AS po ON (po.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_poll_votes AS pv ON (pv.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_topic_notify AS tn ON (tn.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_bookmarks AS b ON (b.topic_id=t.topic_id) WHERE t.topic_deleted='1'";

        if(isset($getData['fid'])){
            $sql.=" AND t.forum_id='" . (int)$getData['fid'] . "'";
        }

        do{
            $this->db->query($sql);

            sleep(2);
        }while($this->db->countAffected());
    }

    public function clearMarkedDeletedTopics($getData,$postData){
        $tid_str='';

        foreach($postData['marked_t_id'] as $tid){
            $tid_str=$tid . ',';
        }

        $tid_str=rtrim($tid_str,',');

        if(!empty($tid_str)){
            $sql="DELETE t, p, po, pv, tn, b FROM " . DB_PREFIX . "community_topics AS t LEFT JOIN " . DB_PREFIX . "community_posts AS p ON (p.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_poll_options AS po ON (po.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_poll_votes AS pv ON (pv.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_topic_notify AS tn ON (tn.topic_id=t.topic_id) LEFT JOIN " . DB_PREFIX . "community_bookmarks AS b ON (b.topic_id=t.topic_id) WHERE t.topic_id IN (" . $tid_str . ")";

            if(isset($getData['fid'])){
                $sql.=" AND t.forum_id='" . (int)$getData['fid'] . "'";
            }

            $this->db->query($sql);
        }
    }

    public function getDeletedPostsTotal($getData){
        $sql="SELECT COUNT(p.post_id) AS total FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) WHERE p.post_deleted='1' AND t.topic_deleted='0'";

        if(isset($getData['fid'])){
            $sql.=" AND p.forum_id='" . (int)$getData['fid'] . "'";
        }

        $query=$this->db->query($sql);

        return $query->row['total'];
    }

    public function getDeletedPosts($getData,$start,$limit){
        $sql="SELECT p.post_id, p.post_subject, p.post_time, p.poster_id, p.post_username, t.topic_id, t.topic_title, f.forum_id, fn.forum_name FROM " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_forum_name_des_rule AS fn ON (fn.forum_id=f.forum_id AND fn.language_id='" . (int)$this->config->get('config_language_id') . "')  LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) WHERE p.post_deleted='1' AND t.topic_deleted='0'";

        if(isset($getData['fid'])){
            $sql.=" AND p.forum_id='" . (int)$getData['fid'] . "'";
        }

        $sql.=" ORDER BY p.post_time DESC LIMIT " . $start . "," . $limit;

        $query=$this->db->query($sql);

        return $query->rows;
    }

    public function restoreDeletedPosts($postData){
        foreach($postData['marked_p_id'] as $id_str){
            list($pid,$tid)=explode('|',$id_str);

            $this->db->query("UPDATE " . DB_PREFIX . "community_posts AS p LEFT JOIN " . DB_PREFIX . "community_topics AS t ON (t.topic_id=p.topic_id) LEFT JOIN " . DB_PREFIX . "community_forum AS f ON (f.forum_id=p.forum_id) LEFT JOIN " . DB_PREFIX . "community_users AS tu ON (tu.user_id=p.poster_id) LEFT JOIN " . DB_PREFIX . "setting AS stp ON (stp.`key`='total_posts') SET p.post_deleted='0', t.topic_replies=t.topic_replies+1, f.forum_posts=f.forum_posts+1, tu.user_posts=tu.user_posts+1, stp.`value`=stp.`value`+1 WHERE p.post_id='" . (int)$pid . "'");

            //clear sql cache
            $encrypt_data=md5($tid);
            $this->sql_cache->delete('viewtopic.posttotal.' . $encrypt_data);
            $this->sql_cache->delete('viewtopic.postids.' . $encrypt_data);
            $this->sql_cache->delete('viewtopic.topicinfo.' . $encrypt_data);
            $this->sql_cache->delete('viewtopic.posts.' . $encrypt_data);
        }

        //clear sql cache
        $this->sql_cache->delete('community.foruminfo.');
    }

    public function clearAllDeletedPosts($getData){
        $sql="DELETE FROM " . DB_PREFIX . "community_posts WHERE post_deleted='1'";

        if(isset($getData['fid'])){
            $sql.=" AND forum_id='" . (int)$getData['fid'] . "'";
        }

        $sql.=" LIMIT 1000";

        do{
            $this->db->query($sql);

            sleep(2);
        }while($this->db->countAffected());
    }

    public function clearMarkedDeletedPosts($getData,$postData){
        $pid_str='';

        foreach($postData['marked_p_id'] as $id_str){
            list($pid)=explode('|',$id_str);

            $pid_str=$pid . ',';
        }

        $pid_str=rtrim($pid_str,',');

        if(!empty($pid_str)){
            $sql="DELETE FROM " . DB_PREFIX . "community_posts WHERE post_id IN (" . $pid_str . ")";

            if(isset($getData['fid'])){
                $sql.=" AND forum_id='" . (int)$getData['fid'] . "'";
            }

            $this->db->query($sql);
        }
    }
    /**************deleted topics and posts end**************/
}
?>